locked
Syntax error in INSERT INTO statement. RRS feed

  • Question

  • User-1494654777 posted

    I am getting below error when I tried to execute insert statement against MS Access database.

    Syntax error in INSERT INTO statement.

    but same statement is executing successfully when I open the mdb file in MS Access.

    What could be the reason?

    Thursday, May 24, 2012 9:03 AM

Answers

  • User-1199946673 posted

    What could be the reason?

    The reason is that when you're connecting with a mdb file using the Jet driver, you're actually not dealing with an Access database, but with a Jet Database Engine, and there some minor differences in the SQL. For example, in Access, the Wildcard character is *, in Jet it is %. Also, you cannot uses a few of the build in functions, Like Replace and Nz. And the list of reserved words is also different:

    List of Reserved Words in Access
    List of Reserved Words in Jet

    You need to show your SQL?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 25, 2012 5:40 AM

All replies

  • User-718146471 posted

    Well, post your code, we can't troubleshoot what we can't see :)

    Thursday, May 24, 2012 9:13 AM
  • User197322208 posted

    Syntax error in INSERT INTO statement.

    but same statement is executing successfully when I open the mdb file in MS Access.

    Help us help you. Show stack trace.

    Thursday, May 24, 2012 9:15 AM
  • User-718146471 posted

    Also, show your connection string because that could also be part of the problem too :)

    Thursday, May 24, 2012 9:17 AM
  • User-1199946673 posted

    What could be the reason?

    The reason is that when you're connecting with a mdb file using the Jet driver, you're actually not dealing with an Access database, but with a Jet Database Engine, and there some minor differences in the SQL. For example, in Access, the Wildcard character is *, in Jet it is %. Also, you cannot uses a few of the build in functions, Like Replace and Nz. And the list of reserved words is also different:

    List of Reserved Words in Access
    List of Reserved Words in Jet

    You need to show your SQL?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 25, 2012 5:40 AM