Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    SQL syntax (2003 sp2)

    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".
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (2003 sp2)

    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:
    <code>
    strSQL = "SELECT CHB_LP_Master_Table_Previous.* INTO [" & expTable & "]" _
    & " IN 'W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'" _
    & " FROM CHB_LP_Master_Table_Previous"
    </code>
    If you never use spaces and/or punctuation in your table names, you can omit the square brackets:
    <code>
    strSQL = "SELECT CHB_LP_Master_Table_Previous.* INTO " & expTable _
    & " IN 'W:PNCADVSpecial AssetsCHBCHB_ARCHIVES.mdb'" _
    & " FROM CHB_LP_Master_Table_Previous"</code>

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax (2003 sp2)

    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?
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (2003 sp2)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax (2003 sp2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •