VBA/Excel/Access/Word/Access/SQL Table Join

Inner Join

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT tblCustomer.txtCustNumber, tblCustomer.txtCustLastName, tblPurchases.txtPurchaseNumber FROM tblCustomer INNER JOIN tblPurchases ON tblCustomer.txtCustNumber = tblPurchases.txtCustNumber;")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub