How can I update a table using Access2Base?

[ColinHume]

I can read the data but not update it. I expect I’m missing something very simple.

Dim ThisDB as Object
Dim FNum as Integer
Dim SQL as String
Set ThisDB = Application.CurrentDB
SQL = "Update Products Set ProductId=2345 where ProductId=0"
ThisDB.RunSQL(SQL)

This produces the error message:

Error #1523 (SQL Error, SQL statement = ‘Update Products Set ProductId=2345 where ProductId=0’) occurred in a call to function ‘Database.RunSQL’

I promise you the Products table does have a record with ProductId=0.

I’d also like to know how I can format this question without having to put in blank lines after every line. If I don’t, the lines are all glued together. Colin Hume

[Ratslinger]

Edited question for clarity

[Ratslinger]

Hello,

You do not state what DB you are using or any other basic information.

Your table and field names are mixed case. They need to be surrounded with quotes. When doing this in a quoted string, you need to use double quotes:

SQL = "Update ""Products"" Set ""ProductID""=2345 where ""ProductID""=0"

As for formatting the code in the question, in the toolbar (upper left when posting) there is an icon for preformatted text

[ColinHume]

Thanks. That certainly works in the simplified version I posted, but my actual SQL is updating a text field, and I get:

Error #1523 (SQL Error, SQL statement = ‘Update “Products” Set “ProductName”=“A Decade of New Assembly” where “ProductId”=0’) occurred in a call to function ‘Database.RunSQL’

I tried it with single quotes round the text value rather than double quotes, but:

Error #1523 (SQL Error, SQL statement = ‘Update “Products” Set “ProductName”=‘A Decade of New Assembly’ where “ProductId”=0’) occurred in a call to function ‘Database.RunSQL’

I’m using the latest version of LibreOffice - Version: 6.3.6.2 (x64) - on a Windows 7 machine. I don’t know what DB I’m using - whatever comes as default. Please let me know if you need further information. ProductName is defined as Text [VARCHAR].

[Ratslinger]

@ColinHume,

Why, Why, Why! Why would you ask one question, get an answer which resolves the problem and then say that is not the actual question/problem? ???

One is not more “simplified than the other”. Both are simple SQL statements in a macro. In a string you use double quotes (that is two sets of quote marks) for field & table names with mixed case. Values are surrounded by an apostrophe. Your statement should be:

SQL = "Update ""Products"" Set ""ProductName"" = 'A Decade of New Assembly' where ""ProductId""=0"

Also, not certain how you can be using software and not know what it is. Base come with two embedded databases. The database being used is displayed on the bottom line of the Base .odb screen.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[ColinHume]

Why? Because I was taught when I worked at IBM to cut the program down to the absolute minimum which demonstrated the problem. I could have posted my entire subroutine, but I didn’t think that would be helpful.

The database is Firebird Embedded. You know where to find this information because you’re an expert on LibreOffice. This is the first time I’ve used it, and that’s only because one of my clients is using it and I need to update the database for them before producing a website page from it. I’m more used to Microsoft Access and MySQL, neither of which has a choice of database, and neither of which requires double quotes round table or field names.

I’ve pasted your solution, but I still get:

Error #1523 (SQL Error, SQL statement = ‘Update “Products” Set “ProductName” = ‘A Decade of New Assembly’ where “ProductId”=0’) occurred in a call to function ‘Database.RunSQL’

[Ratslinger]

Post the problem! Not even the same data types!

I know where to look because when I started to use Base I read the documentation. It states a lot.

Edit:

Test shows it to work from IDE or Form. Further test show the same error when incorrect table or field name is used. My post used the names you posted. Check you field name especially ProductName as this is the difference from the first SQL statement.

[Ratslinger]

Also tested in both HSQL embedded and Firebird embedded without problem.

Edit:

Access2Base is not giving any specific information on the error. Run this:

Update "Products" Set "ProductName" = 'A Decade of New Assembly' where "ProductId"=0

From main Base menu selection Tools->SQL.... There you will get the specific error. Again, having no problem running this unless names are incorrect.

[Ratslinger]

Just noticed. In my first SQL I used ProductID and you stated that worked. However your question, my second statement and your errors all use ProductId. Which is correct?

[ColinHume]

I ran the SQL as you suggested, and got:

caused by
‘isc_dsql_prepare’

No doubt if I studied the manuals I would find out what this means, but it’s the sort of useless message I expect from Microsoft!

Thank you. Changing the capitalisation to ProductID did the trick.

This seems a very strange implementation of SQL. As I said, the versions I’m used to don’t need quotes round table names and field names. SQL Server is, by default, case insensitive when it comes to field names (column names). Neither Microsotf Access nor MySQL are case-sensitive with field names or table names. And LibreOffice isn’t even consistent - I can say SQL = “Select * from Products” and it works without any quotes.

[Ratslinger]

I ran the SQL as you suggested, and got:

caused by ‘isc_dsql_prepare’

No doubt if I studied the manuals I would find out what this means

That is the last line in the error message presented. There are more lines above that further explaining the error. In this case most likely no need for any further reference.