Access2Base problem with RunSql command

Porkie · General User Joined: 13 Mar 2007 Posts: 5

I am trying to use the following command in my Access2Base macros

RunSQL( " UPDATE Follow_ups SET Priority = 2 WHERE Priority = 1 " )

This returns the error #1021 (SQL Error, SQL Statement = ' UPDATE Follow_ups SET Priority = 2 WHERE Priority = 1 ') occurred in a call to RunSQL

I have tried many variants but cannot get anything to not return the same error. Follow_ups is the table name and Priority is the column name type integer

If I paste the example from the Access2Base manual it works, but this is creating a new table. Has anyone got the UPDATE Sql command to work in Access2Base?

Thanks for any help

JPL · General User Joined: 20 Jan 2012 Posts: 8

Hello,

I don't see any obvious reason why it should not work.
Try to paste your SQL statement in the SQL box displayed from the Tools + SQL ... menu. If it works there it should work with the RunSQL command.

Thanks for reporting the result.

Porkie · General User Joined: 13 Mar 2007 Posts: 5

Hi

Thanks for helping, you are right, I don't see any reason either.

However, I ran the command in the SQL box (thanks for pointing that out - I had realised that option was there)

It works if I put " " around the table name and column names. This conflicts with the " " required in the runSQL command.

I tried other delineators ' ' [] {} all don't work either.

I tried to build up the command in a string variable but cannot seem to sort that out either.

From the example in the runSQL section of the manual, and also the coding conventions, it seems the I need to put a _ ch before the command, but that didn't work either! It still seems to require a conflict of "" characters to pick the string element.

Cheers

JPL · General User Joined: 20 Jan 2012 Posts: 8

Hello,

Try

Porkie · General User Joined: 13 Mar 2007 Posts: 5

Brilliant. I have it all working now.

Thanks for your help JPL. If these initials decode as Jean-Pierre Ledure can I say what stunning piece of work you have give us.

I 100% agree with your comment in the "Why Access2Base" section of the manual. I tried programming using the Base API and just gave up after several very frustrating days of effort.

A few months ago I discovered v0.5 of A2B and programmed pretty much the whole database in a couple of days, I am now just tweaking to make it easier to use.

Any chance of an A2B user forum on your website. The manual is excellent, but is missing some of the tweaks you have shown me here, which may well be lost to the community unless they are recorded somewhere more specific.

Thanks again.