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

Establishing Relationships Using Code

   <source lang="vb">

Sub CreateRelation()

   Dim tbl As ADOX.Table
   Dim fk As ADOX.Key
   Dim cat As ADOX.Catalog
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = CurrentProject.Connection
   Set tbl = cat.Tables("tblPeople")
   Set fk = New ADOX.Key
   With fk
       .Name = "PeopleFood"
       .Type = adKeyForeign
       .RelatedTable = "Foods"
       .Columns.Append "FoodID"
       .Columns("FoodID").RelatedColumn = "FoodID"
   End With
   tbl.Keys.Append fk
   Set cat = Nothing
   Set tbl = Nothing
   Set fk = Nothing

End Sub

</source>
   
  

Open the recordset, designating that the source is a SQL statement based on more than one table

   <source lang="vb">

Sub InconsistentUpdates()

   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   rst.ActiveConnection = CurrentProject.Connection
   rst.CursorType = adOpenStatic
   rst.LockType = adLockOptimistic
   rst.Properties("Jet OLEDB:Inconsistent") = True
   rst.Open Source:="Select * from Employees " & _
       "INNER JOIN Projects " & _
       "ON Employees.ClientID = Projects.ClientID", _
       Options:=adCmdText
   "Modify the contents of the foreign key field
   rst("Projects.ClientID") = 1
   rst.Update
   Debug.Print rst("Projects.ClientID")
   rst.Close
   Set rst = Nothing

End Sub

</source>