I have a program that creates an array of part numbers. I also have a database that holds part numbers and all associated information I need for other calculations. How can I search an MSAccess database table against all of the part numbers in the array?
The Array will never have more than 100 items in it, the table has over 7000 records to search from. I've posted my nonworking code hoping someone can point me in the tight direction.

Code:
Dim rs2 As New ADODB.Recordset
        Dim Test1(7000) As String
        Dim Test2(7000) As String
        Dim X As Integer

        'CBOpHwPn(CBOpHwCount) is the Array with multible Part Numbers 
        'CBOpHwCount is the number in the array

        X = 1

        'rs2.Open("select * from tblMain where OrderNumber = '" & CBOpHwPn(CBOpHwCount) & "'", strConn, 2, 2)
        rs2.Open("select * from [tblMain]", strConn2)



        If Not rs2.EOF Then


            If CBDrHDPLTPn(X) > "" And Mid(rs2.Fields("Part_Number").Value, 1, 11) = CBDrHDPLTPn(X) Then
                'X = X + 1
                If Not rs2.EOF Then

                    Test1(X) = rs2.Fields("Part_Number").Value  'Part number from Database
                    Test2(X) = rs2.Fields("Part_Desc").Value    'First of the items to pull from the data base
                    '    'Until CBDrHDPLTPn(X) has completed its loop
                    X = X + 1
                End If
            End If
        End If

        rs2.Close()
Thanks for looking any help would be appreciated