ODBC Error [42000] running certain MS Access Update or Insert Statements

[GStegemann]

Hello Edwig,

the following Update and Insert Statements return the following ODBC errors:

update PropVals set value='StartParm' where objectid = ? and propertyid = 88;
update PropVals set [value]='D:\zrgu' where objectid = ? and propertyid = 59;
insert into PropVals (value, objectid, propertyid) values ('D:\zrgu', ?, 59);

       0             update PropVals set value = 'StartParm' where objectid =? and propertyid = 88..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3503][Microsoft][ODBC Microsoft Access Driver] Syntaxfehler in UPDATE-Anweisung.

       0             update PropVals set [ value ]= 'D:\zrgu' where objectid =? and propertyid = 59..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-1002][Microsoft][ODBC Microsoft Access Driver] Unzulässiges Einklammern des Namens '[ value ]'.

       0             insert into PropVals ( value , objectid , propertyid ) values ( 'D:\zrgu' ,?, 59)..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3502][Microsoft][ODBC Microsoft Access Driver] Syntaxfehler in der INSERT INTO-Anweisung. 

I don't see any syntax errors in these statements. Is "value" a reserved word? But the affected table has a column named "value" which cannot be renamed. Is there a way to escape such a column name?

Best regards, Gerhard

[GStegemann]

I did some more testing. In fact "VALUE" is a reserved word. And I think I found the reason for the syntax error message:

Update PropVals set [ value ]='CCCC' where objectid=2487;
[Microsoft][ODBC Microsoft Access Driver] Unzulässiges Einklammern des Namens '[ value ]'

The problem is that ODBC Query adds spaces between the square brackets and the identities (table or column names). That is not accepted by MS Access.

Escapeing a reserved word just with square brackets works:

Update PropVals set [value]='CCCC' where objectid=2487;
OK.

That needs to be fixed.

[edwig]

The standard way (The SQL Standard ISO 9075) to escape an identifier that is also a reserved word is to enclose it in double quotes. The escape in angular brackets is a Microsoft specific extension of SQL-Server, so never the best bet. If you want to try it out, this is what I did to test your error message in an MS-Access ODBC driver:

CREATE TABLE test ( one INT, two CHAR(100), [Value] INT );

INSERT INTO test VALUES (1,'testin',2); SELECT * FROM test; SELECT Value FROM test; -- Works fine, resonds with the inserted values

-- Now test with the insert :variable1 input LONG; :variable1 = 88;

INSERT INTO test(one,two,[Value]) VALUES (2,'Other test',?); -- BOOM ! Error INSERT INTO test(one,two,"Value") VALUES (2,'Other test',?); -- Works fine !!

SELECT * FROM test; -- Responds with two lines (1 and 2) and 88 for the value!

My conclusiion: It works just fine. Nothing needs to be fixed. Please test again with double quotes as the identifier escape.

[GStegemann]

I ran tests with your script:

Status Affected rows Result                                                                        

       0             CREATE TABLE test
(
one INT,
two CHAR(100),
[Value] CHAR(128)
)..... : OK     
       1             INSERT INTO test VALUES (1,'testin',2)..... : OK                              
       1             SELECT * FROM test ..... : OK                                                 
       1             SELECT Value FROM test ..... : OK                                             
       1             :variable LONG : OK                                                           
       1             :variable = 88 : OK                                                           
       1             INSERT INTO test(one,two,[Value]) VALUES (2,'Other test',?)..... : OK         
       1             INSERT INTO test ( one , two , "Value" ) VALUES (2, 'Other test' ,?)..... : OK
       3             SELECT * FROM test..... : OK                                                  

Surprisingly the script worked. Event the first INSERT statement did not fail. Wonder why. In my script using square brackets does not work at all.

However, trying to create column "Value" as LONGCHAR does not work. In my project this column is of type LONGCHAR:

Status Affected rows Result                                                                                                                                                                                                                                                                                                       

       0             [Value] LONGCHAR
)..... : Error in SQL statement: ODBC-call returned [-1] : [42000][-3500][Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.                                                                                          

I think LONGCHAR is a valid MS Access data type.

Anyway, what speaks against to not add spaces using square brackets escapeing identies?