SQL error with ACE.OLEDB

Discussion in 'Databases' started by NeilG3, May 1, 2019.

  1. We recently completed updating our Access database to .accdb format and using the ACE.OLEDB driver. Last week (Apr. 29th), one of our tracking functions began to fail with the message:
    Microsoft Access Database Engine error '80040e14'
    Syntax error in INSERT INTO statement

    After testing, including checking the Microsoft documentation, the on-line SQL syntax verification that shows the syntax to be 100% correct, and other sources, we are still getting the error. The current syntax is:
    INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('" & LogVal & "', '" & WhoAdm & "', '" & DidWhat & "');

    Everything is simple plain text except LogVal, which is a date & time compilation that is converted to text. Our own syntax checker returns the result which appears to be valid:
    INSERT INTO AdTrack (LogIn, Admin, Action) VALUES ('2019/5/1/11', 'John Smith', 'Upload of email attachment failed at 11:30:00 AM: ');

    This same syntax worked for over 20 years using the .mdb format and MS-Access driver.

    Any help and insights are appreciated.
  2. Elshadriel

    Elshadriel Winhost Staff

    It looks correct to me. The only thing I can think of to help you troubleshoot the problem is output the SQL string with a few iterations to verify that the syntax is correct.
  3. Yes, I've been trying different iterations from the day the insert process failed. I have yet to find one source that suggests that the syntax is incorrect, except for the error message on the server, and even that worked for years until a few days ago.
  4. Well, after a lot of frustration, including tests that used straight text in place of the parameterized content, I went back to the "old" microsoft driver. The tracking function now works with no syntax errors. I wish I could use the newer ACE.OLEDB driver, but it seems to be rather quirky with Access database files.
  5. Elshadriel

    Elshadriel Winhost Staff

    Sorry to hear that, and thanks for posting the solution or work around. I'm sure it will help others out in the future.
  6. Did you try this with the more standard Date/Time Delimiters: #019/5/1/11# instead of the implicit conversion of the string delimiter used in the sample you posted.

    It has been my experience that newer versions of Access, i.e. ACE, tend to be less tolerant of ambiguity.

Share This Page