Hello,
I have developed a module that compares data from a table in the current database with data that is contained on a table in an external database and updates a field in the internal table. The code that I came up with works, but I feel that it is inefficient and creates too much processing overhead.
Basically, I created two recordsets (r1, r2) which compares each record in r1 with each record in r2 and updates the required field in r1 accordingly. However, if r1 contains 1004 records and r2 contains 578 records ... well, I'm sure you get the picture. Here's what I've got:
If rs1.RecordCount <> 0 Then
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1!fld1 = rs2!fld1 And rs1!fld2 = rs2!fld2 _
And rs1!fld3 = rs2!fld3 And rs1!fld4 = rs2!fld4 Then
rs1.Edit
rs1!fld5 = rs2!fld5
rs1.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End If
Any suggestions that will increase performance, efficiency, and lessen processing overhead would be greatly appreciated.
Thanks in advance ERM
I have developed a module that compares data from a table in the current database with data that is contained on a table in an external database and updates a field in the internal table. The code that I came up with works, but I feel that it is inefficient and creates too much processing overhead.
Basically, I created two recordsets (r1, r2) which compares each record in r1 with each record in r2 and updates the required field in r1 accordingly. However, if r1 contains 1004 records and r2 contains 578 records ... well, I'm sure you get the picture. Here's what I've got:
If rs1.RecordCount <> 0 Then
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1!fld1 = rs2!fld1 And rs1!fld2 = rs2!fld2 _
And rs1!fld3 = rs2!fld3 And rs1!fld4 = rs2!fld4 Then
rs1.Edit
rs1!fld5 = rs2!fld5
rs1.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End If
Any suggestions that will increase performance, efficiency, and lessen processing overhead would be greatly appreciated.
Thanks in advance ERM