Compare tables in VBA Access

Question

I am trying to compare two tables in VBA access to see if there are any new clients in one that need to be added to another. I am starting to develop the comparison loop but for some reason it isn't working. It is saying the client ID field has a value of 6 for all the records in the one table, which is not true. I'm not sure if I'm referencing it right in the If statement. Any help on this topic would be greatly appreciated.

dte = InputBox("What date was the Data Dump run?", "Please Input a date")

tableName = "FN_DataDump_ALL_" & dte
Set dataDump = db.OpenRecordset(tableName, dbOpenDynaset, dbEditAdd)
Set clientTable = db.OpenRecordset("Clients", dbOpenDynaset, dbEditAdd)
With dataDump
    .MoveFirst
    Do
    If dataDump("[Client ID]") <> clientTable("[Client ID]") Then
         MsgBox (dataDump("[Client ID]"))
    clientTable.MoveNext
    Else: MsgBox ("Match.")
    .MoveFirst
End If
    Loop Until .EOF
End With

UPDATE:

For some reason VBA keeps saying it can't find the table DataDump, when it's opening correctly and everything. I throws this error when it trys to run the SQL so I think there is something wrong with my statement.

Set db = CurrentDb


dte = InputBox("What date was the Data Dump run?", "Please Input a date")

tableName = "FN_DataDump_ALL_" & dte
Set DataDump = db.OpenRecordset(tableName, dbOpenDynaset, dbEditAdd)
Set clientTable = db.OpenRecordset("Clients", dbOpenDynaset, dbEditAdd)


MySQL = "SELECT DISTINCT CL.[Client ID] " & _
        "INTO Clients " & _
        "FROM Clients AS CL " & _
        "Where CL.[Client ID] NOT IN (SELECT DD.[Client ID] FROM  DataDump AS DD) "

UPDATE #2:

Now it is saying I have a syntax error and I am really confused as to what it is.

"Where CL.[Client ID] NOT IN (SELECT DD.[Client ID] ""FROM"" &  tableName ""as DD"") "

Does your database include a table or query named DataDump? — HansUp
Not that name specifically but I opened it in VBA using that name so it shouldn't matter right? — user3681360
OK, I see. DataDump is a recordset object. No, you can't SELECT FROM a recordset. — HansUp
Oh I see. So I should change it to the table name it is in the database? — user3681360
Yes, I think you want to use the value of your tableName variable there. — HansUp

Johnny Bones Johnny Bones · Accepted Answer · 2014-11-14T19:37:24

First of all, you've got a .MoveFirst where you should have a .MoveNext

However, a better way would be to use a NOT IN query. I would do this:

tableName = "FN_DataDump_ALL_" & dte
Set dataDump = db.OpenRecordset(tableName, dbOpenDynaset, dbEditAdd)
Set clientTable = db.OpenRecordset("Clients", dbOpenDynaset, dbEditAdd)

MySQL = "SELECT DISTINCT CL.[Client ID] " & _
    "INTO TempClients " & _
    "FROM Clients AS CL " & _
    "Where CL.[Client ID] NOT IN (SELECT DD.[Client ID] FROM " & tableName & " AS DD) "

DoCmd.RunSQL (MySQL)

Now you've got your list of Client IDs in the dataset called "rec". This is going to be more efficient than looping through every record and comparing IDs, unless your tables are pretty small and then the difference in proficiency will be negligible.

Compare tables in VBA Access

2 Answers