How to compare recordsets in access vba

**ADezii** · Sep 10 '09, 10:35 PM

I'm a little confused myself, but I'll at least post some code that I created which will cover the first 2 conditions:

An exact Match on all 4 Fields
Exact Match on Fields 3 and 4 only, no Match on Fields 1 and 2

The code will cross reference 'every' Record in Table1 (rs1) against 'every' Record in the Standard Values Table (Table2 - rs2). The Fields [Field1], [Field2], [Field3], and [Field4] exist in both Tables, are NUMERIC (LONG INTEGER), and are synchronized. The code will record 2 unique circumstances that you had previously indicated, and are referenced by items 1 and 2 above. The Absolute Record Position for both Recordsets will also be contained in the Output. The Output can easily be redirected to a Table, Text File, Excel Worksheets, etc. The code is easily expandable to include additional functionality.

You must keep in mind 2 important points:

Processing of 18,000 Records involves 324,000,000 iterations of the Nested Loops.
The Output will only be valid as long as the 'State' of both Tables remains constant, namely: no Additions, Deletions, Edits, etc.

Code:

Dim MyDB As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim lngRS1 As Long
Dim lngRS2 As Long

lngRS1 = 0
lngRS2 = 0

Set MyDB = CurrentDb()
Set rs1 = MyDB.OpenRecordset("Table1", dbOpenSnapshot, dbOpenForwardOnly)
Set rs2 = MyDB.OpenRecordset("Table2", dbOpenSnapshot)

Do While Not rs1.EOF
 lngRS1 = lngRS1 + 1
  Do While Not rs2.EOF
   lngRS2 = lngRS2 + 1
   If (rs1![Field1] = rs2![Field1]) And (rs1![Field2] = rs2![Field2]) And _
                      (rs1![Field3] = rs2![Field3]) And (rs1![Field4] = rs2![Field4]) Then
     Debug.Print "**Match on all 4 Fields: rs1(Record " & lngRS1 & ") <==> rs2(Record " & lngRS2 & ")"
   ElseIf (rs1![Field1] <> rs2![Field1]) And (rs1![Field2] <> rs2![Field2]) _
          And (rs1![Field3] = rs2![Field3]) And (rs1![Field4] = rs2![Field4]) Then
     Debug.Print "  |- *Field3 and Field4 Match: rs1(Record " & lngRS1 & ") <==> rs2(Record " & lngRS2 & ")"
   'Else        unsure of OP's intention
     'Debug.Print "Mismatch: rs1(Record " & lngRS1 & ") <> rs2(Record " & lngRS2 & ")"
   End If
      rs2.MoveNext
  Loop
    rs2.MoveFirst
    rs1.MoveNext
    lngRS2 = 0
Loop


rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

Sample OUTPUT:

Code:

  |- *Field3 and Field4 Match: rs1(Record 1) <==> rs2(Record 1)
**Match on all 4 Fields: rs1(Record 1) <==> rs2(Record 8)
  |- *Field3 and Field4 Match: rs1(Record 3) <==> rs2(Record 7)
  |- *Field3 and Field4 Match: rs1(Record 6) <==> rs2(Record 4)
**Match on all 4 Fields: rs1(Record 7) <==> rs2(Record 5)
**Match on all 4 Fields: rs1(Record 7) <==> rs2(Record 6)

**atksamy** · Sep 11 '09, 04:29 AM

Thanks for the replies.

I have a hitch . One of the fields is not numeric. Field 4 is numeric as well as alphabetic. is there any way to overcome this

thanks

**ADezii** · Sep 11 '09, 10:55 AM

Originally posted by atksamy

Thanks for the replies.

I have a hitch . One of the fields is not numeric. Field 4 is numeric as well as alphabetic. is there any way to overcome this

thanks

To be absolutely sure, you can coerce Field4 to a Numeric Data Type, then make the comparison. It will not make any difference whether or not it actually is, as in:

Code:

If rs1![Field4] = Val(rs2![Field4]) ...

**NeoPa** · Sep 11 '09, 07:39 PM

Originally posted by ADezii

To be absolutely sure, you can coerce Field4 to a Numeric Data Type, then make the comparison.

Wouldn't that cause a code failure (crash) for records where the data was alphabetic?

**ADezii** · Sep 11 '09, 11:34 PM

Originally posted by NeoPa

Wouldn't that cause a code failure (crash) for records where the data was alphabetic?

Not that I am aware of, NeoPa. The Val(<any Alpha>) would return Zero (0) and the comparison would fail.

**NeoPa** · Sep 12 '09, 12:26 AM

Ah you're right about it not failing, but is there not a need to compare alpha strings too. This would surely treat dissimilar strings as equal no?

**ADezii** · Sep 12 '09, 01:20 AM

Originally posted by NeoPa

Ah you're right about it not failing, but is there not a need to compare alpha strings too. This would surely treat dissimilar strings as equal no?

As stated by the OP:

I have a hitch . One of the fields is not numeric. Field 4 is numeric as well as alphabetic. is there any way to overcome this

According to the OP (as indicated above), the only Field in question is [Field4] which can be Alpha or Numeric, so I assume that [Field4] would be our only concern other than is it Alpha/Numeric in 1 or both Tables, but I could be incorrect in my assumption. To cover all grounds would then be:

Code:

If Val(rs1![Field4]) = Val(rs2![Field4]) ... ?

This Thread has got me confused so forgive me if I ramble on...

B.T.W - To process 2 Tables consisting of 18,000 Records each would require 324,000,000 iterations of Nested Loops. The processing of 11,000,000 iterations took 17.5 minutes meaning the Total Processing Time for the 324,000,000 iterations would be approximately 8.6 hours.

**NeoPa** · Sep 12 '09, 12:38 PM

Originally posted by ADezii

To cover all grounds would then be:

Code:

If Val(rs1![Field4]) = Val(rs2![Field4]) ... ?

So, if rs1![Field4]="ABC" & rs2![Field4]="Texas" then both resolve to 0 :

Code:

If Val(rs1![Field4]) = Val(rs2![Field4])

returns TRUE yet :

Code:

If rs1![Field4] = rs2![Field4]

returns FALSE.

Am I missing something (as I did earlier to be sure)?

**ADezii** · Sep 12 '09, 06:34 PM

Originally posted by NeoPa

So, if rs1![Field4]="ABC" & rs2![Field4]="Texas" then both resolve to 0 :

Code:

If Val(rs1![Field4]) = Val(rs2![Field4])

returns TRUE yet :

Code:

If rs1![Field4] = rs2![Field4]

returns FALSE.

Am I missing something (as I did earlier to be sure)?

I see what you mean, NeoPa, and you definately are not missing anything. I think at this point it would be wise to wait until the OP intervenes again to clarify some key issues - signing off, but keeping tuned-in, for now. Take care.

**atksamy** · Sep 14 '09, 04:11 AM

Thanks for replying.
Sorry for this confusing thread.
Anyway as Neopa had said i would want to compare the alphabetic strings in the field as well, to check if they are the same.

**atksamy** · Sep 14 '09, 09:43 AM

also would checking using ascii values work

**ADezii** · Sep 14 '09, 01:35 PM

Originally posted by atksamy

also would checking using ascii values work

Would the potential Strings 'always' represent Numbers ("43", "128", "598", ...), or could they represent anything as indicated by NeoPa in Post #23:
Code:
```
if rs1![Field4]="ABC" & rs2![Field4]="Texas"
```
Can you run into a situation where Field1 in rs1 is Numeric (897), while in rs2 it is Text ("897").

If it will be possible to compare Non-Numeric Strings, is the comparison Case-Sensitive, namely:

Code:

NeoPa = NEOpa evaluates to TRUE       'Case-Insensitive
NeoPa = NEOpa evaluates to FALSE      'Case-Sensitive

**atksamy** · Sep 15 '09, 04:01 AM

1. The potential strings could represent anything strings or numerals.

2. Yes it is possible. As the field property of rs2 is text so "897" could be in text format.

3. At this point i dont think it has to be case sensitive,

so NeoPa = NEOpa evaluates to TRUE is correct.

**NeoPa** · Sep 15 '09, 09:20 AM

Originally posted by atksamy

so NeoPa = NEOpa evaluates to TRUE is correct.

I believe this is a selectable setting and is controlled by the Option statement. This is usually found at the head of all modules. Mine is (I suspect most are too) set to :

Code:

Option Compare Database

This is case non-specific comparisons.

Just be aware that while this may be default behaviour it is probably not wise to rely on this setting being used universally in your code.

**ADezii** · Sep 15 '09, 07:08 PM

Originally posted by atksamy

1. The potential strings could represent anything strings or numerals.

2. Yes it is possible. As the field property of rs2 is text so "897" could be in text format.

3. At this point i dont think it has to be case sensitive,

so NeoPa = NEOpa evaluates to TRUE is correct.

Due to the uncertainty surrounding [Field4] as to TEXT/NUMERIC in Table1/Table2, I feel as though the best bet would be to convert Values in rs1![Field4] and rs2![Field4] to Strings, then perform a Case-Insensitive Comparison regardless of System Settings. This should alleviate potential problems as illustrated by NeoPa, nEoPa, NEoPA, whatever, in Post Numbers 23 and 29. The revised code, checking for Matches on ALL 4 Fields, and for Matches on Fields [Field3] and [Field4] only. would now be (Code Line changes in Lines 21 and 25):

Code:

Dim MyDB As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim lngRS1 As Long
Dim lngRS2 As Long

lngRS1 = 0
lngRS2 = 0

Set MyDB = CurrentDb()
Set rs1 = MyDB.OpenRecordset("Table1", dbOpenSnapshot, dbOpenForwardOnly)

Set rs2 = MyDB.OpenRecordset("Table2", dbOpenSnapshot)

Do While Not rs1.EOF
 lngRS1 = lngRS1 + 1
  Do While Not rs2.EOF
   lngRS2 = lngRS2 + 1
   If (rs1![Field1] = rs2![Field1]) And (rs1![Field2] = rs2![Field2]) And _
                      (rs1![Field3] = rs2![Field3]) And _
                      StrComp(CStr(rs1![Field4]), CStr(rs2![Field4]), vbTextCompare) = 0 Then
     Debug.Print "**Match on all 4 Fields: rs1(Record " & lngRS1 & ") <==> rs2(Record " & lngRS2 & ")"
   ElseIf (rs1![Field1] <> rs2![Field1]) And (rs1![Field2] <> rs2![Field2]) _
          And (rs1![Field3] = rs2![Field3]) And _
          StrComp(CStr(rs1![Field4]), CStr(rs2![Field4]), vbTextCompare) = 0 Then
     Debug.Print "  |- *Field3 and Field4 Match: rs1(Record " & lngRS1 & ") <==> rs2(Record " & lngRS2 & ")"
   'Else        unsure of OP's intention
     'Debug.Print "Mismatch: rs1(Record " & lngRS1 & ") <> rs2(Record " & lngRS2 & ")"
   End If
      rs2.MoveNext
  Loop
    rs2.MoveFirst
    rs1.MoveNext
    lngRS2 = 0
Loop


rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

How to compare recordsets in access vba

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment