Error with executing SQL

[txchem]

I found this code from an earlier post however I keep getting an error on the ".Execute" statement, the error is a runtime error with Syntax error in Update Statement and when I hit debug it goes to the .Execute piece of code. IS there a reference or something else I need to turn on?

Code:
Public Sub Update_and_Insert_Access_Table()
    
    Dim dbConnection As Object  'ADODB.Connection
    Dim dbCommand As Object     'ADODB.Command
    Dim ExcelTable As String
    Dim SQL As String
    
    ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"
    
    Set dbConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    dbConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\an\HRS SD Performance Data\Capacity DB.accdb;"
    
    Set dbCommand = CreateObject("ADODB.Command") 'New ADODB.Command
    
    With dbCommand
        .ActiveConnection = dbConnection
                    
        'Update Volume field in existing records
            
        SQL = "UPDATE AssignedVol_tbl A" & _
              " INNER JOIN " & ExcelTable & " X" & _
              " ON A.ID_Unique = X.ID_Unique" & _
              " SET A.Volume = X.Volume"
        .CommandText = SQL
        .Execute

        'Insert new records

        SQL = "INSERT INTO AssignedVol_tbl" & _
              " SELECT * FROM " & ExcelTable & " X" & _
              " WHERE X.ID_Unique NOT IN (SELECT ID_Unique FROM AssignedVol_tbl)"
        .CommandText = SQL
        .Execute
    End With
    
    dbConnection.Close
    Set dbCommand = Nothing
    Set dbConnection = Nothing
    
End Sub

 

[MoshiM]

end the commands with a semi-colon

 

[txchem]

end the commands with a semi-colon Compile error: Expected: expression

I tried putting a semi-colon after .Execute and get

 

[RoryA]

That connection string looks odd to me. I think it should be:

ExcelTable = "[Excel 12.0;HDR=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"

 

[txchem]

That connection string looks odd to me. I think it should be:

ExcelTable = "[Excel 12.0;HDR=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"

Sorry I had updated it to run with my Spreadsheet and db. Correct is below

Public Sub Update_and_Insert_Access_Table()
   
    Dim dbConnection As Object  'ADODB.Connection
    Dim dbCommand As Object     'ADODB.Command
    Dim ExcelTable As String
    Dim SQL As String
   
    ExcelTable = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\foster.john\Desktop\Production Schedule 2022  DugTest-1.xlsm].[Line14]"
   
    Set dbConnection = CreateObject("ADODB.Connection") 'New ADODB.Connection
    dbConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\US5MS0005\_Group Folders\Public\Production Data\Production Database\dbPlantData_be.accdb;"
   
    Set dbCommand = CreateObject("ADODB.Command") 'New ADODB.Command
   
    With dbCommand
        .ActiveConnection = dbConnection
                   
        'Update Volume field in existing records
           
        SQL = "UPDATE tblProcessOrder A" & _
              " INNER JOIN " & ExcelTable & " X" & _
              " ON A.intPO = X.intPO" & _
              " SET A.dtmScheduledDate = X.dtmScheduledDate, SET A.txtSpecInstruct = X.txtSpecInstruct,A.intSAPIDFK = X.intSAPIDFK, A.intQuantityNeeded = X.intQuantityNeeded, A.txtUoM = X.txtUoM, A.intExtruderIDFK = X.intExtruderIDFK "
           
             
        .CommandText = SQL
        .Execute
        

        'Insert new records

        SQL = "INSERT INTO tblProcessOrder" & _
              " SELECT * FROM " & ExcelTable & " X" & _
              " WHERE X.intPO NOT IN (SELECT intPO FROM tblProcessOrder)"
        .CommandText = SQL
        .Execute
       
    End With
   
    dbConnection.Close
    Set dbCommand = Nothing
    Set dbConnection = Nothing
   
End Sub

That connection string looks odd to me. I think it should be:

ExcelTable = "[Excel 12.0;HDR=YES;DATABASE=C:\Users\lueg\Desktop\DB Macro Test.xlsm].[rawdata$]"

 

[RoryA]

That still looks wrong.

 

[txchem]

That still looks wrong.

I changed to the way to had it getting rid of the IMEX and ACCDB, still get the .Execute error

 

[RoryA]

What is Line14?

 

[txchem]

Line14 is the Sheet name in Excel

 

[RoryA]

Then it should have a $ sign on the end in your connection string.