I am trying to compare 2 tables from 2 different databases using ODBC.
After researching the web and the groups I decided to use arrays as
looping through recordsets was painfully slow. My code runs well and I
accomplish my objective to compare what amounts to one table of over
4000 records and a second table of over 2800 records in 37 seconds. I
am happy with the outcome and the results are written to an Access
database with 3 tables showing the differences both ways and where the
data is equal. I am providing the below code as a reference to my
question.
Is there a more efficient way of comparing this data so that I can see
the differences each way and where the data is the same? This is just
the start of a very large project and I will be comparing many tables
across different databases and I am looking to make this as efficient
as possible. Is it possible to compare the data both ways and keep it
to a single loop or is there a more creative solution available.
Thanks for any suggestions.
' Fill Arrays from recordsets
If not Rs_01.EOF then arr_01 = Rs_01.GetRows
If not Rs_02.EOF then arr_02 = Rs_02.GetRows
' Compare Array1 to Array2 where equal add to recordset Rs_PackageEqual
' where different add to recordset Rs_PackageDiffPMA
For x = 0 to Ubound(arr_01,2)
For y = 0 to Ubound(arr_02,2)
If arr_01(0,x) = arr_02(0,y) Then
Rs_PackageEqual.AddNew
Rs_PackageEqual.Fields("Packages") = arr_01(0,x)
Rs_PackageEqual.Update
Exit For
ElseIf y = Ubound(arr_02,2) Then
Rs_PackageDiffPMA.AddNew
Rs_PackageDiffPMA.Fields("Packages") = arr_01(0,x)
Rs_PackageDiffPMA.Update
End If
Next
Next
' Compare Array2 to Array1 where different add to recordset
Rs_PackageDiffPricing
For x = 0 to Ubound(arr_02,2)
For y = 0 to Ubound(arr_01,2)
If arr_02(0,x) = arr_01(0,y) Then
Exit For
ElseIf y = Ubound(arr_01,2) Then
Rs_PackageDiffPricing.AddNew
Rs_PackageDiffPricing.Fields("Packages") = arr_02(0,x)
Rs_PackageDiffPricing.Update
End If
Next
Next
'