Unanswered: Query works with MS SQL Server, not with MS Access ODBC
I am writing an application in VC++ 2005 w/ADO.Net and I create a few tables in an empty database in the beginning of the program by issuing a sequence of SQL commands to the appropriate provider (SqlClient and OdbcClient). The problem is that the error messages are very short and cryptic. When I use the SqlClient to connect to the local SQL/Express server the sequence succeeds. However, when I use the OdbcClient to write to an empty MS Access file, the last SQL command fails with the error message:
"ERROR  [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition."
Here are the SQL commands, executed in that sequence:
CREATE TABLE DlIndexTable (SessionStartTime DATETIME NOT NULL PRIMARY KEY, SchemaID INTEGER NOT NULL)
CREATE TABLE DlChannelInfoTable (ChannelID INTEGER NOT NULL PRIMARY KEY, ChannelName VARCHAR (255) NOT NULL, ChannelUnits VARCHAR (32), ChannelOrder INTEGER NOT NULL, ChannelSampleRate REAL NOT NULL)
CREATE TABLE DlSchemaTable (SchemaID INTEGER NOT NULL, ChannelID INTEGER NOT NULL REFERENCES DlChannelInfoTable, CONSTRAINT SchPriKey PRIMARY KEY (SchemaID, ChannelID))
There is one difference that I don't believe affects anything - I've tried it either way and it didn't make a difference. The data type of the "Timestamp" field is shown as "real" above but in reality I query the database schema collections and from the "DataTypes" I match the type that corresponds to System.Double, which for MS SQL is real but for MS Access/ODBC happens to be "double".
I am hoping that someone could spot the problem, i.e., what is acceptable in MS SQL syntax but unacceptable in MS Access syntax. I will take a systematic debugging approach ("divide and conquer") so I will hopefully resolve this by the end of the day, but I wouldn't mind if someone would save me a few hours. Thank you in advance.
This was quicker than I thought (or I got lucky). It turns out SQL Server will let you use reserved words for column names but Access - will not. "Timestamp" is a reserved word in both. I apologize for the silly question.