Excel VBA – ADO Connection to MS Access DB – Filtering results

I have a MS Access Database where I store some information of products and I have these fields:

ID, Customer Part No., Description, Price, Observations

I also have an Excel file where I have a column called Customer Part No. and in this column I paste the Customer’s Part I want to filter.

I need a way to make a query to my access DB using Excel and VBA to return only the records where I have a match between in the Customer Part No.

At the moment I managed to connect to the database and retrieve all records. However I still didn’t figured out how to select only the records where I have a match, I imagine it would be something in the SQL query but I don’t know how it would go.

Sub ADO_Connection()

'Creating objects of Connection and Recordset
    Dim conn As New Connection, rec As New Recordset
    Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "C:SourcingDatabase.accdb"
'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
    conn.Open connString
'the query I want to run on the database.
    query = "SELECT * from Data;"

'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn
    
    
    
'clearing the content of the cells
    Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
    If (rec.RecordCount <> 0) Then
        Do While Not rec.EOF
            Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
            rec.Fields(1).Value
            rec.MoveNext
        Loop
    End If
'closing the connections
    rec.Close
    conn.Close

End Sub

Answer

Found the solution in case anyone is interested:

Below is my code:

Sub ADO_Connection()

'Creating objects of Connection and Recordset
    Dim conn As New Connection, rec As New Recordset
    Dim DBPATH, PRVD, connString, query, partlist As String

    Dim cel As Range
    Dim selectedRange As Range
    
    Set selectedRange = Application.Selection
    
    partlist = "("
    
    For Each cel In selectedRange.Cells
        partlist = partlist & "'" & cel.Value & "',"
    Next cel
    
    partlist = Left(partlist, Len(partlist) - 1)
    partlist = partlist & ")"
    MsgBox partlist

'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "C:SourcingDatabase.accdb"
'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
    conn.Open connString
'the query I want to run on the database.
    query = "SELECT * from Data WHERE CustomerPartNumber IN " & partlist & ";"
    MsgBox query

'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn
            
'clearing the content of the cells
    Cells.ClearContents
    
    
'getting data from the recordset if any and printing it in column A of excel sheet.
    If (rec.RecordCount <> 0) Then
        Do While Not rec.EOF
            Range("C" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = rec.Fields(1).Value
            
            rec.MoveNext
        Loop
    End If
'closing the connections
    rec.Close
    conn.Close

End Sub