compare all fields in access using vba/sql

Question

I know I can compare values between two tables, but I have not needed to do it for more than 2 or 3 fields up to this point so comparing them each individually hasnt been an issue, I used code such as this:

DoCmd.RunSQL "INSERT INTO Issues 
            SELECT Eligibility.[Member Id]
                 , Eligibility.[Sex Code]
                 , Eligibility.State 
            FROM Eligibility LEFT JOIN Ref 
            ON Eligibility.[Sex Code] = Ref.[Sex Code] 
            WHERE (((Ref.[Sex Code]) Is Null));"

now however, i need to compare about 140 different fields. is there a better way to do this than writting 140 sql statements and running them all one by one?

i want it to find where fields dont contain the same info and then pull the entire row from both tables,or at the very least the value in the 5th column, member id, and then i can run another query to pull the entire row off of that value if need be (so i can look at both at the same time) and paste them into another table and highlight the cells where the mismatches occur.

both tables are in the same database both tables have the same structure, but the second table might not have all of the values from the first, so i need to find a way to have it match the rows based on the member ID before it starts comparing the rows.

Answer 1

You can compare using DAO pretty easily. Using the .Fields() argument on a recordset you get all the different fields in the actual recordset.

This lets you do something like:

Sub exampleSQLComparison()

    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    '.... set those recordsets via SQL statements

    For Each f In rs1.Fields
        If rs1.Fields(f) <> rs2.Fields(f) Then
            Debug.Print "Mismatch found for " + f
        End If

    Next f

End Sub

If your queries are similar and the only thing you are changing is a single field (for example an ID) you should be able to modify the logic accordingly.