Why do I get syntax error message on this INSERT INTO ?

I keep getting "-2147217900 [Microsoft][ODBC Excel Driver] Syntax error in INSERT INTO statement." on an INSERT INTO query

Aside from the fact that I am not using parameters (like I should be), why am I suddenly getting this error message?


What I have checked so far:

• I googled the problem and thought that maybe the 'UserName' could be a reserved name so I altered it to 'UsrName' but it still occurs.
• The workbook exists, as does the sheet, as does the field (header) names.
• The workbook is not in use at time of writing. (I check with VBA before creating the connection string)
• The record being inserted is after row 8 so it cant be a problem with the data types in the Excel table.
• (update) Altered the 1.01 value to remove the ' prefix/suffix (so it wouldnt be written as a string). Still occurs
• (update) Altered the table name from 'log' to 'MyLog'. Error still occurs.

Could it be that one of the other field names be reserved?

Could it be that the table name (log) is a reserved name? (Unlikely, changed to MyLog and still get error)

Is it because I am specifying the primary key instead of letting it be assigned by auto-increment? (Doubtful as I have been using INSERT INTO with manually set PK into excel workbooks for a while now without any issues)

Can anyone tell me what is causing this?


My Query

Please Login or Register  to view this content.

My Connection String

Please Login or Register  to view this content.

After trying many different solutions, I finally got it. After I changed the field name 'DateTime' to something else, the problem went away.

"Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth."

If you want/need to use reserved words as field names, and I wouldn't recommend it, you can usually enclose them in [].

INSERT INTO [log$] (ID, [DateTime], VerMMDB, UsrName, Activity) VALUES(11, 42086.52, 1.01, 'EXCEL', 'Upgraded BDF from v5 to v6')

Originally Posted by Norie

If you want/need to use reserved words as field names, and I wouldn't recommend it, you can usually enclose them in [].

Thanks for providing the alternative option Norie. I had read that suggestion online but I mentally recoiled for some reason! (In the end, I went with changing the 'DateTime' to 'LogTime')