Help with looping through recordsets

  • Thread starter Thread starter Freddie
  • Start date Start date
F

Freddie

I need some advice please. I have 2 recordsets RsX and RsY. Both are
identical in schema. How could I efficiently, in code, loop through both
Rs's row by row, field by field and compare the values of each field? When I
find a field in RsX that is different from the same field in RsY, I would
like to update the field in RsY with the value in RsX, and then go on to the
next field and repeat the update process if the fields are different until
I've looped through the entire recordset. Can some please point me in the
right direction as to how I would perform this in VBA code.

Mush thanks,


Fred A.
 
Freddie said:
I need some advice please. I have 2 recordsets RsX and RsY. Both are
identical in schema. How could I efficiently, in code, loop through
both Rs's row by row, field by field and compare the values of each
field? When I find a field in RsX that is different from the same
field in RsY, I would like to update the field in RsY with the value
in RsX, and then go on to the next field and repeat the update
process if the fields are different until I've looped through the
entire recordset. Can some please point me in the right direction as
to how I would perform this in VBA code.
Click to expand...

Probably the most efficient way is actually to do this with a series of
update queries acting on the underlying tables, one for each field.
This is feasible so long as there's a unique key on which the tables can
be joined.

If you really have to do it with recordsets, or you want to do it as an
exercise, you might take the following routine as a jumping-off place.
I had this lying around -- it uses recordsets to compare two queries on
a record-by-record basis, to see if they return identical results.

'----- start of code -----
Sub CompareQueries(strQuery1 As String, strQuery2 As String)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim lngRecCnt As Long
Dim lngErrCnt As Long
Dim intFld As Integer

Const conMAX_ERRS = 25

Set db = CurrentDb()

Set rs1 = db.OpenRecordset(strQuery1)
Set rs2 = db.OpenRecordset(strQuery2)

rs1.MoveFirst
rs2.MoveFirst

Do Until rs1.EOF Or rs2.EOF

lngRecCnt = lngRecCnt + 1

If rs2.Fields.Count <> rs1.Fields.Count Then
Debug.Print "Field count mismatch!"
Stop
Exit Do
End If

For intFld = 0 To (rs1.Fields.Count - 1)
If rs2.Fields(intFld).Name <> rs1.Fields(intFld).Name Then
Debug.Print "Field name mismatch!"
Stop
End If
If rs2.Fields(intFld).Type <> rs1.Fields(intFld).Type Then
Debug.Print "Field type mismatch!"
Stop
End If
If rs2.Fields(intFld).Value <> rs1.Fields(intFld).Value Then
Debug.Print "Field [" & rs1.Fields(intFld).Name & "] : "
& _
"query 1 value = " & rs1.Fields(intFld).Value & _
", query 2 value = " & rs2.Fields(intFld).Value
lngErrCnt = lngErrCnt + 1
If lngErrCnt > conMAX_ERRS Then
Exit Do
End If
End If


Next intFld

rs1.MoveNext
rs2.MoveNext
Loop

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

Set db = Nothing

Debug.Print "Examined " & lngRecCnt & " records and found " & _
lngErrCnt & " errors."

End Sub
'----- end of code -----
 
Dirk,
Thanks for the expert advice. I do how ever have another question. Both
of my recordsets with have the same fields, and the fields will be named the
same as well. Is there a way I can store the names of the fields, perhaps in
an array, and use this array in my loop? If so how would this work, or do
you have another solution to this issue? I'm trying not to create such a
long routine, I figure if I can store a reference to the field names some
where it may cut back on the amount of code, I'll have to write. Does this
make sense to you. You advice is helpful, thanks,

Fred.



Dirk Goldgar said:
Freddie said:
I need some advice please. I have 2 recordsets RsX and RsY. Both are
identical in schema. How could I efficiently, in code, loop through
both Rs's row by row, field by field and compare the values of each
field? When I find a field in RsX that is different from the same
field in RsY, I would like to update the field in RsY with the value
in RsX, and then go on to the next field and repeat the update
process if the fields are different until I've looped through the
entire recordset. Can some please point me in the right direction as
to how I would perform this in VBA code.
Click to expand...

Probably the most efficient way is actually to do this with a series of
update queries acting on the underlying tables, one for each field.
This is feasible so long as there's a unique key on which the tables can
be joined.

If you really have to do it with recordsets, or you want to do it as an
exercise, you might take the following routine as a jumping-off place.
I had this lying around -- it uses recordsets to compare two queries on
a record-by-record basis, to see if they return identical results.

'----- start of code -----
Sub CompareQueries(strQuery1 As String, strQuery2 As String)

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim lngRecCnt As Long
Dim lngErrCnt As Long
Dim intFld As Integer

Const conMAX_ERRS = 25

Set db = CurrentDb()

Set rs1 = db.OpenRecordset(strQuery1)
Set rs2 = db.OpenRecordset(strQuery2)

rs1.MoveFirst
rs2.MoveFirst

Do Until rs1.EOF Or rs2.EOF

lngRecCnt = lngRecCnt + 1

If rs2.Fields.Count <> rs1.Fields.Count Then
Debug.Print "Field count mismatch!"
Stop
Exit Do
End If

For intFld = 0 To (rs1.Fields.Count - 1)
If rs2.Fields(intFld).Name <> rs1.Fields(intFld).Name Then
Debug.Print "Field name mismatch!"
Stop
End If
If rs2.Fields(intFld).Type <> rs1.Fields(intFld).Type Then
Debug.Print "Field type mismatch!"
Stop
End If
If rs2.Fields(intFld).Value <> rs1.Fields(intFld).Value Then
Debug.Print "Field [" & rs1.Fields(intFld).Name & "] : "
& _
"query 1 value = " & rs1.Fields(intFld).Value & _
", query 2 value = " & rs2.Fields(intFld).Value
lngErrCnt = lngErrCnt + 1
If lngErrCnt > conMAX_ERRS Then
Exit Do
End If
End If


Next intFld

rs1.MoveNext
rs2.MoveNext
Loop

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

Set db = Nothing

Debug.Print "Examined " & lngRecCnt & " records and found " & _
lngErrCnt & " errors."

End Sub
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Freddie said:
Dirk,
Thanks for the expert advice. I do how ever have another
question. Both of my recordsets with have the same fields, and the
fields will be named the same as well. Is there a way I can store the
names of the fields, perhaps in an array, and use this array in my
loop? If so how would this work, or do you have another solution to
this issue? I'm trying not to create such a long routine, I figure if
I can store a reference to the field names some where it may cut back
on the amount of code, I'll have to write. Does this make sense to
you. You advice is helpful, thanks,
Click to expand...

I don't follow you, Fred. The example I posted, for example, doesn't
have any hard-coded references to the field names. It just uses a
numeric index (intFld) into the recordsets' Fields collection. Why
would you need an array of field names? If you're sure the recordsets
are structurally identical, then you can drop the code I had that was
checking for name or type mismatches.
 
Back
Top