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

Inner Join

   <source lang="vb">

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

</source>