ADO/VBA to compare records

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have an ADO recordset. I need to compare the values in Field1 to
determine if they appear in an array I have populated. Second, I would like
to compare each element of my array to determine if it is in Field 1 of my
recordset. My objective is to have a list for items in recordset, but not
in array and those is array, but not in recordset.

TIA
 
Joe said:
I have an ADO recordset. I need to compare the values in Field1 to
determine if they appear in an array I have populated. Second, I would
like to compare each element of my array to determine if it is in Field 1
of my recordset. My objective is to have a list for items in recordset,
but not in array and those is array, but not in recordset.

TIA
Click to expand...


Public Sub CompareArray()

Dim alngTest(2)
Dim lngLoop As Long
Dim rst As ADODB.Recordset
Dim boolFound As Boolean

For lngLoop = LBound(alngTest) To UBound(alngTest)
alngTest(lngLoop) = lngLoop
Next lngLoop

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.Source = "SELECT TestID FROM tblTest"
.Open
Do Until .EOF
For lngLoop = LBound(alngTest) To UBound(alngTest)
boolFound = False
If .Fields("TestID").Value = alngTest(lngLoop) Then
boolFound = True
Exit For
End If
Next lngLoop
Debug.Print "Value " & rst.Fields("TestID").Value & " ";
Debug.Print IIf(boolFound, "was ", "was not ");
Debug.Print "found in the array"
.MoveNext
Loop
.Close
End With

End Sub
 
Brendan Reynolds said:
Public Sub CompareArray()

Dim alngTest(2)
Dim lngLoop As Long
Dim rst As ADODB.Recordset
Dim boolFound As Boolean

For lngLoop = LBound(alngTest) To UBound(alngTest)
alngTest(lngLoop) = lngLoop
Next lngLoop

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.Source = "SELECT TestID FROM tblTest"
.Open
Do Until .EOF
For lngLoop = LBound(alngTest) To UBound(alngTest)
boolFound = False
If .Fields("TestID").Value = alngTest(lngLoop) Then
boolFound = True
Exit For
End If
Next lngLoop
Debug.Print "Value " & rst.Fields("TestID").Value & " ";
Debug.Print IIf(boolFound, "was ", "was not ");
Debug.Print "found in the array"
.MoveNext
Loop
.Close
End With

End Sub
Click to expand...


Oops. While the above would work, it would be inefficient to have the
'boolFound' variable set to False on every pass through the For ... Next
loop. It only needs to be set to False once, before the start of the For ...
Next loop.

boolFound = False
For lngLoop = LBound(alngTest) To UBound(alngTest)
If .Fields("TestID").Value = alngTest(lngLoop) Then
boolFound = True
Exit For
End If
Next lngLoop
 
Back
Top