Comparing recordset values in Access VBA

Question

I am trying to compare two recordsets in access VBA to check whether the values within the two tables are the same or whether they differ. Both recordsets have the same structure (field headings) and record IDs and I'm trying to check whether a field value for a record matches the corresponding field value in the second recordset. The record ID field name is MATNR.

I think I've managed to loop through the records and fields for the 1st recordset but I'm unsure how to loop through and compare these records with the second. Also, is there a smarter way to compare the recordsets other than If rs1.Fields(fld.Name) = rs2.Fields(fld.Name)

Any help will be greatly appreciated.

    Public Sub VerifyRecords()

        Dim rs As DAO.Recordset
        Dim rs1 As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim rs3 As DAO.Recordset
        Dim fld As DAO.Field
        Dim sSQL As String
        Dim sSQL1 As String
        Dim sSQL2 As String

    Set rs = CurrentDb.OpenRecordset("R2_Tables_to_Compare1") 'This table lists the upload tables to query and their corresponding target tables
    Set rs3 = CurrentDb.OpenRecordset("RecordValueComparisonResults") 'Write the results of the record vlaue comparison to here

    '**************************************************************************************
    'This SQL statement selects all records from the upload table

        sSQL = "SELECT * "
        sSQL = sSQL & " FROM " & rs(0)

    Set rs1 = CurrentDb.OpenRecordset(sSQL)

    '**************************************************************************************
    'This SQL statement selects only those records that are applicable in the target table

        sSQL1 = "SELECT " & rs(1) & ".* FROM " & rs(1) & " INNER JOIN " & rs(0) & " ON " & rs(1) & ".MATNR = " & rs(0) & ".MATNR"

    Set rs2 = CurrentDb.OpenRecordset(sSQL1)
    '**************************************************************************************
     Do While Not rs1.EOF
                For Each fld In rs1.Fields
                       If rs1.Fields(fld.Name) = rs2.Fields(fld.Name) Then
                            Debug.Print rs1.Fields("MATNR"), rs2.Fields("MATNR"), fld.Name, rs1.Fields(fld.Name), rs2.Fields(fld.Name)
                    End If
                Next fld
            rs1.MoveNext
    Loop


rs.Close
rs1.Close
rs2.Close
rs3.Close

Set rs = Nothing
Set rs1 = Nothing    
Set rs2 = Nothing    
Set rs3 = Nothing

    End Sub

If you're dealing with records in a RDBMS and you start thinking in terms of loops, you're doing it wrong. Pretty sure whatever this is doing can be expressed as a plain SQL query. — Mathieu Guindon

Ian Berryman Ian Berryman · Accepted Answer · 2017-07-15T21:21:01

Below are two options, although the QUERY OPTION is faster and better practice when working in Access and any relational DB:

QUERY OPTION: This query could be passed into a recordset and the recordset would contain only the matching values between the fields in the two tables. Then you could loop through that new recordset and print or process as necessary using a single loop.
```
SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
```

LOOP OPTION: If you are intent on looping through both recordsets, use this code. There is probably a more efficient way to do this, especially since this method uses four nested loops which is a no-no. I would highly recommend the QUERY OPTION.

While Not rs1.EOF
   While Not rs2.EOF
     For Each fld1 in rs1.Fields
       For Each fld2 in rs2.Fields
         If rs1.Fields(fld1.Name) = rs2.Fields(fld2.Name) Then
             Debug.Print rs1.Fields("MATNR"), rs2.Fields("MATNR"), fld1.Name, 
                         rs1.Fields(fld1.Name), rs2.Fields(fld2.Name)
         End If
       Next fld2
     Next fld1
       rs2.MoveNext
   Wend
   rs2.MoveFirst
   rs1.MoveNext
 Wend

Comparing recordset values in Access VBA

1 Answers