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

Posted on 2007-10-13
Last Modified: 2013-12-25
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
Question by:BrianBeck
    LVL 42

    Expert Comment

    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.


    Author Comment

    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?

    Author Comment

    fyi, the SQL string includes a trailing apostrophe

    Author Comment

    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?
    LVL 58

    Accepted Solution

    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'
    WHERE POSTCODE = '2059'

    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!

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now