Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Syntax Error In FROM Clause

    Hi all,

    I am receiving a "Syntax Error in FROM Clause" when I try to run this code

    Code:
        'SQL
         strSQL1 = "SELECT * INTO " & strTblArchive2 & " FROM " & strTblCopy1
         strSQL1 = strSQL1 & " SELECT * INTO " & strTblArchive3 & " FROM " & strTblCopy2
            
        'Do it
         conn.Execute (strSQL1)
    I checked all of the $variables, they are all correct.
    Am I missing a space or something else I overlooked?

    Is there a limit to the length of the SQL string?
    The first part is 65 characters.
    When I hover over conn.Execute (strSQL1) in debug, it appends three dots "..." to the end of the string
    Here is the first part of the SQL$
    Code:
    strSQL1 = "SELECT * INTO " & strTblArchive2 & " FROM " & strTblCopy1
    Debug.Print strSQL1
    SELECT * INTO CustomerARCHxxxxxxxxxxxxxxxx FROM CustomerOLDxxxxxx
    thx
    w

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, you cannot execute batch commands in Access. Execute each query separately.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Agree
    I don't think you can execute multiple SQL statements in that manner. but if you could you would need to separate each statement with a semi colon
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Sinndho/healdem - thanks.

    I think I had incorrect syntax as well as I was trying to append to existing tables, not create new tables.

    Here is my final working SQL
    Code:
         strSQL1 = ""
         strSQL1 = "INSERT INTO " & strTblArchive2 & " SELECT * FROM " & strTblCopy1
         conn.Execute (strSQL1)
    
         strSQL2 = ""
         strSQL2 = "INSERT INTO " & strTblArchive3 & " SELECT * FROM " & strTblCopy2
         conn.Execute (strSQL2)
    thx
    w

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Just a word of note...

    Code:
    strSQL1 = ""
    and
    Code:
    strSQL2 = ""
    Aren't necessary. Even if you haven't declared the variables as Strings (in which case that still isn't the correct way to go about it), VBA will let them function as Strings, as that's what the information being parsed to it is.


    Secondly, you don't need two different variables if all you're doing is executing them.

    Code:
    strSQL = "INSERT INTO " & strTblArchive2 & " SELECT * FROM " & strTblCopy1
    conn.Execute (strSQL)
    
    strSQL = "INSERT INTO " & strTblArchive3 & " SELECT * FROM " & strTblCopy2
    conn.Execute (strSQL)
    Will achieve the same thing, with less variables being declared (and therefore being more efficient when it comes to the garbage collector cleaning it up later), and less lines of code to execute, resulting in a faster (albeit negligably so) execution at runtime.
    Looking for the perfect beer...

Posting Permissions

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