Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

"Syntax error in INSERT INTO statement" error (VB6/ Jet)

Dear Gurus
I receive a "syntax error in INSERT INTO statement" error when executing the following statement, and would be grateful of your assistance with the correction.  I believe that the required text delimiters are in place.
        SQLStr1 = "INSERT INTO " & tbl & " IN '" & txt_Output_Db & _
            "' SELECT FROM '" & txt_db1 & "' WHERE POSTCODE = '" & pc & "'"
        DbsConnection1.Execute SQLStr1
Thanks in advance
  • 4
1 Solution
You need a list of columns between "SELECT" and "FROM".  You must have the same number of columns, with the same datatypes, in the same order as the target table.  However, if the source table and the target table have identical structures, then you can just do:  

 SQLStr1 = "INSERT INTO " & tbl & " IN " & txt_Output_Db & _
            " SELECT * FROM " & txt_db1 & " WHERE POSTCODE = '" & pc & "'"

Note that I have also removed what appear to be extraneous quote marks.

BrianBeckAuthor Commented:
Hi dqmq
Thank you for your suggestions.  I've firstly replaced my SQL string with your edition.  I've also ensured that the table structure of the output database is exactly the same as the source database (e.g. using Access's 'paste as structure only' command).
However, after the above, I still get the INSERT INTO syntax error.  To assist, below is the SQL string generated from the code:
INSERT INTO DB_ALL IN D:\pc_test_struct.mdb SELECT FROM D:\pc_test.mdb WHERE POSTCODE = '2059
Can you help please?
BrianBeckAuthor Commented:
fyi, the SQL string includes a trailing apostrophe
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

BrianBeckAuthor Commented:
Hi dqmq
I've found a Microsoft article regarding reserved words:
I've checked the column names in the Access tables and am very sure that none are reserved.

By the way, given the importance of correct SQL syntax, and that SQL is used so frequently in software today, does Microsoft have a web site that allows me to upload an SQL string and fetch a detailed answer as to why syntax is incorrect?
You insert into a table, but you also need to select from a table, not just from a database. For example:

INSERT INTO DB_ALL (Field1, Field2) IN 'D:\pc_test_struct.mdb'
SELECT Foo,Bar FROM A_Table IN 'D:\pc_test.mdb'

If you want to use the same table name in both cases, and if both have exactly the same field names, you can try this:

        SQLStr1 _
            = " INSERT INTO " & tbl & " IN '" & txt_Output_Db & "'" _
            & " SELECT * FROM " & tbl & " IN '" & txt_db1 & "'" _
            & " WHERE POSTCODE = '" & pc & "'"
        DbsConnection1.Execute SQLStr1

Good luck!
BrianBeckAuthor Commented:
Hi harfang - yes, the table names are exactly the same in both databases, and both tables have exactly the same structure.  You SQL string worked perfectly, thank you.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now