• SQL syntax (2003 sp2)

    Author
    Topic
    #449345

    This SQL copies a table to an external database and names it “expTable”:

    strSQL = “SELECT CHB_LP_Master_Table_Previous.* INTO expTable” _
    & ” IN ‘W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'” _
    & ” FROM CHB_LP_Master_Table_Previous”

    The problem is “expTable” is supposed to be the variable not the table name. I forgot the quotes of course. So I correct the SQL and low the variable is recognized and holds the proper table name:

    strSQL = “SELECT CHB_LP_Master_Table_Previous.* INTO ‘” & expTable & “‘” _
    & ” IN ‘W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'” _
    & ” FROM CHB_LP_Master_Table_Previous”

    Now I get an error “Incomplete Query clause”.

    Viewing 0 reply threads
    Author
    Replies
    • #1101512

      There shouldn’t be single quotes around the table name, and I’d add square brackets instead to accomodate for a table name with spaces or punctuation:

      strSQL = "SELECT CHB_LP_Master_Table_Previous.* INTO [" & expTable & "]" _
      & " IN 'W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'" _
      & " FROM CHB_LP_Master_Table_Previous"

      If you never use spaces and/or punctuation in your table names, you can omit the square brackets:

      strSQL = "SELECT CHB_LP_Master_Table_Previous.* INTO " & expTable _
      & " IN 'W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'" _
      & " FROM CHB_LP_Master_Table_Previous"

      • #1101515

        It’s a good thing I’m not a brain surgeon. I completely forgot I appended a date, with spaces, to the table name. I otherwise never use spaces. But now I have to ask why the single quotes around the database name? Or did Access just add them as it often does in queries?

        • #1101516

          An external database places a different role in a query than a table. Access places single quotes around the name. You could use double quotes instead, but when you save the query, Access will replace them with single quotes again.

          • #1101519

            Thank you so much for your help and understanding. An by the way, with the added brackets the SQL runs without error.

    Viewing 0 reply threads
    Reply To: SQL syntax (2003 sp2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: