Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question 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 [42000] [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))

    CREATE TABLE DlTextDataTable (SessionStartTime DATETIME NOT NULL REFERENCES DlIndexTable, Timestamp float NOT NULL, Channel01data VARCHAR (255), Channel02data VARCHAR (255), Channel03data VARCHAR (255), Channel04data VARCHAR (255), Channel05data VARCHAR (255), Channel06data VARCHAR (255), Channel07data VARCHAR (255), Channel08data VARCHAR (255), Channel09data VARCHAR (255), Channel10data VARCHAR (255), Channel11data VARCHAR (255), Channel12data VARCHAR (255), Channel13data VARCHAR (255), Channel14data VARCHAR (255), Channel15data VARCHAR (255), Channel16data VARCHAR (255), CONSTRAINT TxtPriKey PRIMARY KEY (SessionStartTime, Timestamp))

    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.

    Kamen

  2. #2
    Join Date
    Dec 2005
    Posts
    74
    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.
    Kamen
    Last edited by KamenG; 07-11-06 at 11:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •