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

Establishing Relationships Using Code

 
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

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

 
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