Error 424 when concatenating fields in a VBA SQL statement

Question

I wrote the following SQL statement in VBA, but I'm getting Error 424 ("Object Required") when I go to execute this statement at the string variable strSQL3 (the VBA editor just highlights the whole SQL string). The statement below is a part of a series of statements that, when executed, import an Excel spreadsheet into my Access database as a temporary table, update statuses, add new records, then drops the temporary table.

     Private Sub btnImport_Click()

'create a new file system object that will check for conditions and import the file as a new table if a valid name is chosen

    Dim FSO As New FileSystemObject
    Dim strSQL, strSQL2, strSQL3, strSQL4 As String
    Dim dateToday As String
    Dim tableTest As Object
    Dim fieldNew As Object
    Dim db As DAO.Database
    Dim Iss As DAO.Recordset
    Dim Temp As DAO.Recordset
    Dim reccordsAdded As Long
    
    dateToday = Date
    Set db = CurrentDb
    'Set Iss = db.OpenRecordset("IssuesDemo")
    
    'If no file name in box
    If Nz(Me.txtFileName, "") = "" Then
        MsgBox "Please choose a file."
        Exit Sub
    End If
    
    'If a file name is in box and the file can be located
    If FSO.FileExists(Me.txtFileName) Then
        
        ImportExcel.ImportExcel Me.txtFileName, "TempTable"
       
    'once it imports the table, it then adds today's date (the upload date)
        
        strSQL = "ALTER TABLE TempTable ADD COLUMN Upload_Date DATE;"
        strSQL2 = "UPDATE TempTable SET Upload_Date = '" & Date & "'"
       
       
       'This SQL Statement inserts the data from TempTable into the Issues table that does not match anything currently in the issues table.
       
       
        strSQL3 = "UPDATE IssuesDemo SET IssuesDemo.Status = 'Closed' " & _
                  " WHERE NOT EXISTS " & _
                  " (SELECT 1 FROM TempTable WHERE" & _
                  "'" & IssuesDemo.[Provider Identifier] & IssuesDemo.[Caresite] & IssuesDemo.[Care Address] & "'=  " & _
                  "'" & TempTable.[Provider Identifier] & TempTable.[Caresite] & TempTable.[Address Composite (Caresite) (Care Site)] & "')"

                  
       
       
        strSQL4 = "INSERT INTO IssuesDemo " & _
                " ([Provider Identifier], [Provider], " & _
                " [Caresite], [Care Address], " & _
                " [Bing Address], [Upload Date]) " & _
                " SELECT " & _
                " TempTable.[Provider Identifier], TempTable.[Provider], TempTable.[Caresite], " & _
                " TempTable.[Address Composite (Caresite) (Care Site)] as [Care Address], " & _
                " TempTable.[Bing Suggested Postal Address (Caresite) (Care Site)] as [Bing Address], " & _
                " TempTable.[Upload_Date] as [Upload Date] " & _
                " FROM TempTable " & _
                " WHERE NOT EXISTS " & _
                " (SELECT 1 FROM IssuesDemo WHERE " & _
                    " IssuesDemo.[Provider Identifier] = TempTable.[Provider Identifier] " & _
                    " AND " & _
                    " IssuesDemo.[Care Address] = TempTable.[Address Composite (Caresite) (Care Site)] " & _
                    " AND " & _
                    " IssuesDemo.[Caresite] = TempTable.[Caresite]) "
''                    " AND " & _
''                    " IssuesDemo.[Upload Date] = TempTable.[Upload_Date]) "

 '       strSQL4 =
              
        db.Execute strSQL, dbFailOnError
        db.Execute strSQL2, dbFailOnError
        db.Execute strSQL3, dbFailOnError
        db.Execute strSQL4, dbFailOnError
        
        'This displays a message box that confirms to the user that the operation succeeded and that X records were added.
        recordsAdded = db.RecordsAffected
        MsgBox "Operation Successful!  You added " & recordsAdded & " records to the database."
        
        'This deletes the temporary table.
        DoCmd.RunSQL ("DROP TABLE TempTable;")
        
    Else
    'Error message if file can't be found
        MsgBox "File not found."
    End If
    

End Sub

My goal here was to compare two sets of concatenated fields together. I want to do this because many of the records are similar from one table to the other, with subtle differences in one of those three fields; there isn't a unique identifier that is common between the two tables here.

Everything else in my VBA code executes just fine. I appreciate any help you can give.

Answer 1

When referring to actual table columns, not values from outside (like textboxes on forms), the table.field names must be part of the SQL string.

strSQL3 = "UPDATE IssuesDemo SET IssuesDemo.Status = 'Closed' " & _
          " WHERE NOT EXISTS " & _
          " (SELECT 1 FROM TempTable WHERE " & _
          " IssuesDemo.[Provider Identifier] & IssuesDemo.[Caresite] & IssuesDemo.[Care Address]  = " & _
          " TempTable.[Provider Identifier] & TempTable.[Caresite] & TempTable.[Address Composite (Caresite) (Care Site)] )"

What happens in your code is: VBA sees IssuesDemo.[Provider Identifier] as a variable, and assumes there is an object IssuesDemo with the property [Provider Identifier].