Excel vba command to retrieve specific information from multiple access table

[william2306]

Hello,
I have a problem with importing data from ms access to excel with <acronym title="visual basic for applications">VBA</acronym>. I use "ADODB" to connect excel with acces.
I have a few tables in ms access. I want to get the ClientDesc and ProductDesc when I input on TrackNo and with a command button to run the VBA. I tried it with inner join (incomplete, without ProductTB), but i only get the first row of information.
so how can i get all the respective/ required info (ClientDesc and ProductDesc).
Thank you for your help.


below is my current code in excel module.

Option Explicit
Public Const DBLink As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\USER\Desktop\Mother.accdb;"
Public Sub SearchTRK(TrackNo As String)
  
    Dim CN As ADODB.Connection
    Dim Rs As ADODB.Recordset    
    Set Rs = New ADODB.Recordset   
    
    Set CN = New ADODB.Connection
    CN.Open DBLink
      
     Set Rs = CN.Execute("select MotherTb.Customer, ClientTb.ClientDesc from MotherTb inner join ClientTb on Mother.Customer = ClientTb.ClientID")
    
     If Not Rs.EOF Then
        Cells(2, 2) = Rs("ClientDesc")
    Else
        MsgBox "Record not found"
    End If
    
    Set Rs = Nothing
    
End Sub

current code in excel sheet

Private Sub Search1_Click()
SearchTRK (Cells(1, 2))
End Sub

Example of table in access
MotherTb

TrackNo	Customer	Product
2017-001	1	1
2017-002	1	2
2017-003	2	3
2017-004	1	4

<tbody>
</tbody>

ClientTb

ClientID	ClientDesc
1	AAA
2	BBB

<tbody>
</tbody>

ProductTb

ProductID	ProductDesc
1	Item A
2	Item B
3	Item C
4	Item D

<tbody>
</tbody>

Example in Excel (End-product)

TrackNo	2017-003 (input)
Client	BBB (output)
Product	Item C (output)

<tbody>
</tbody>