Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    79

    Error In Insert Into Statement

    Hi all,

    Using Access 2007 w/VBA and SQL
    Do you see a prblem with the INSERT INTO Statement?
    I'm not seeing it

    Error:
    Run-time error '3134':
    Syntax error in INSERT INTO statement
    Snippet:
    Code:
    sSQL = ""
         sSQL = sSQL & "INSERT INTO " & sTableTmp
         sSQL = sSQL & "SELECT * "
         sSQL = sSQL & "FROM " & sTable & ";"
         db.Execute (sSQL)
    Full code below
    Thx
    w

    Code:
    Option Compare Database
    
    Sub UpdateMasterParts()
        
        'Environment
         DoCmd.SetWarnings False
        
        'Variables
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim sSQL As String
         Dim sTable As String
         Dim sStatus As String
         Dim sTableTmp As String
         
         Set db = CurrentDb
         sTableTmp = "xPartData"
    
        'Get selected table name
         sTable = Forms!Parts!lstTblsImportMaster.Value
         
         If TableExists(sTableTmp) Then
            sSQL = ""
            sSQL = "DROP TABLE " & sTableTmp
            db.Execute (sSQL)
         End If
         
        'Add temp table
         strStatus = "Adding tmp table..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         sSQL = ""
         sSQL = sSQL & "CREATE TABLE " & sTableTmp
         sSQL = sSQL & "("
         sSQL = sSQL & "PT Text,"
         sSQL = sSQL & "Part Text"
         sSQL = sSQL & ")"
         db.Execute (sSQL)
         
        'Append records from selected table
         strStatus = "Appending records..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         sSQL = ""
         sSQL = sSQL & "INSERT INTO " & sTableTmp
         sSQL = sSQL & "SELECT * "
         sSQL = sSQL & "FROM " & sTable & ";"
         db.Execute (sSQL)
         
        'Delete from tmp table if exists on Target table
         strStatus = "Deleting existing data..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
        
         sSQL = ""
         sSQL = sSQL & "DELETE DISTINCTROW " & sTableTmp & " .*"
         sSQL = sSQL & "FROM " & sTableTmp & " "
         sSQL = sSQL & "INNER JOIN tbl_Parts "
         sSQL = sSQL & "ON " & sTableTmp & ".Part=tbl_Parts.Part;"
         
        'Append remaining records to Target table
         strStatus = "Appending records..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         sSQL = ""
         sSQL = sSQL & "INSERT INTO tbl_Parts (Part)"
         sSQL = sSQL & "SELECT Part "
         sSQL = sSQL & "FROM " & sTable & ";"
         db.Execute (sSQL)
         
        'Tidy up
    
         db.Execute (sSQL)
         db.Close
         varStatus = SysCmd(acSysCmdClearStatus)
        
        'Environment
         DoCmd.SetWarnings True
    
    End Sub
    
    Function TableExists(strTable As String) As Boolean
    
        ' Comments  : Checks to see if table exists in current Access database
        ' Parameters: strTable - name of the table to check for
        ' Returns   : True if exists, False if not
        ' Created   : 01/01/08 Sig VanDamme
        ' Link      : http://www.nimbleuser.com/_Blogs/Developers/Developers/Access_VBA_Function_to_Check_if_Table_Exists.aspx
        ' --------------------------------------------------
    
        On Error GoTo DOESNOTEXIST
        Dim oDb As Database
        Dim oTd As TableDef
    
        Set oDb = CurrentDb
        Set oTd = oDb.TableDefs(strTable)
        TableExists = True
        oDb.Close
        Exit Function
    
    DOESNOTEXIST:
        TableExists = False
    
    EXIT_PROC:
        End Function

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    does your query work if you run it as a query?
    ifd not refien the query there before stuffing it into VBA.


    you may be better off with a SELECT INTO rather than INSERT
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    633
    both of your insert statements seem to be missing a space before SELECT

  4. #4
    Join Date
    Feb 2012
    Posts
    79
    Thanks,

    Appears to be working correctly Using SELECT..INTO

    thx
    w
    Code:
    Option Compare Database
    
    Sub UpdateMasterParts()
        
        'Environment
         DoCmd.SetWarnings False
        
        'Variables
         Dim db As DAO.Database
         Dim rs As DAO.Recordset
         Dim sSQL As String
         Dim sTable As String
         Dim sStatus As String
         Dim sTableTmp As String
         
         Set db = CurrentDb
         sTableTmp = "xPartData"
    
        'Get selected table name
         sTable = Forms!Parts!lstTblsImportMaster.Value
         
         If TableExists(sTableTmp) Then
            sSQL = ""
            sSQL = "DROP TABLE " & sTableTmp
            db.Execute (sSQL)
         End If
         
        'Append records from selected table to a tmp table
         strStatus = "Appending records..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         sSQL = ""
         sSQL = sSQL & "SELECT " & sTable & ".* "
         sSQL = sSQL & "INTO " & sTableTmp & " "
         sSQL = sSQL & "FROM " & sTable & ";"
         db.Execute (sSQL)
         
        'Delete from tmp table if exists on Target table
         strStatus = "Deleting existing data..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
        
         sSQL = ""
         sSQL = sSQL & "DELETE DISTINCTROW " & sTableTmp & " .*"
         sSQL = sSQL & "FROM " & sTableTmp & " "
         sSQL = sSQL & "INNER JOIN tbl_Parts "
         sSQL = sSQL & "ON " & sTableTmp & ".Part=tbl_Parts.Part;"
         
        'Append remaining records to Target table
         strStatus = "Appending records..."
         varStatus = SysCmd(acSysCmdSetStatus, strStatus)
         
         sSQL = ""
         sSQL = sSQL & "INSERT INTO tbl_Parts (Part)"
         sSQL = sSQL & "SELECT Part "
         sSQL = sSQL & "FROM " & sTable & ";"
         db.Execute (sSQL)
         
        'Tidy up
    
         db.Close
         varStatus = SysCmd(acSysCmdClearStatus)
        
        'Environment
         DoCmd.SetWarnings True
    
    End Sub
    
    Function TableExists(strTable As String) As Boolean
    
        ' Comments  : Checks to see if table exists in current Access database
        ' Parameters: strTable - name of the table to check for
        ' Returns   : True if exists, False if not
        ' Created   : 01/01/08 Sig VanDamme
        ' Link      : http://www.nimbleuser.com/_Blogs/Developers/Developers/Access_VBA_Function_to_Check_if_Table_Exists.aspx
        ' --------------------------------------------------
    
        On Error GoTo DOESNOTEXIST
        Dim oDb As Database
        Dim oTd As TableDef
    
        Set oDb = CurrentDb
        Set oTd = oDb.TableDefs(strTable)
        TableExists = True
        oDb.Close
        Exit Function
    
    DOESNOTEXIST:
        TableExists = False
    
    EXIT_PROC:
        End Function

Posting Permissions

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