OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Access2Base problem with RunSql command

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Porkie
General User
General User


Joined: 13 Mar 2007
Posts: 5

PostPosted: Sat May 19, 2012 5:36 pm    Post subject: Access2Base problem with RunSql command Reply with quote

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
Back to top
View user's profile Send private message
JPL
General User
General User


Joined: 20 Jan 2012
Posts: 10

PostPosted: Sat May 19, 2012 10:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Porkie
General User
General User


Joined: 13 Mar 2007
Posts: 5

PostPosted: Sun May 20, 2012 3:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
JPL
General User
General User


Joined: 20 Jan 2012
Posts: 10

PostPosted: Sun May 20, 2012 11:18 pm    Post subject: Reply with quote

Hello,

Try
Code:

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

Doubling double-quotes in a character string in Basic inserts exactly 1 double-quote in the string itself without conflicting with the surrounding double-quotes.

The version 0.7.5 of Access2Base (coming soon) will allow the square brackets [] as delimitors and replace them with the appropriate delimitor expected by the actual database system.

Hoping this will help.
Back to top
View user's profile Send private message
Porkie
General User
General User


Joined: 13 Mar 2007
Posts: 5

PostPosted: Tue May 22, 2012 3:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group