Access VBA创建表关系时如何指定连接类型？

Great question! When you use SQL's ADD CONSTRAINT syntax to create foreign keys in Access, you’re correct that it only establishes basic referential integrity and defaults to an inner join for queries. To explicitly set a join type (like left outer, right outer), you’ll need to use Access’s DAO Object Model—this is the underlying framework Access uses to manage relationship properties that aren’t exposed via standard SQL.

Step-by-Step Solution with DAO #

DAO lets you fully control relationship attributes, including the default join type used in queries. Here’s how to create or update relationships with custom join types:

1. Create a New Relationship with a Custom Join Type #

This example creates a left outer join between an Orders child table and Customers parent table, with update cascade enabled:

Sub CreateCustomRelationship()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    
    ' Reference the current database
    Set db = CurrentDb()
    
    ' Clean up existing relationship if it exists (avoids duplicate errors)
    On Error Resume Next
    db.Relations.Delete "FK_Orders_Customers"
    On Error GoTo 0
    
    ' Initialize the relationship object
    ' Parameters: Relationship Name, Child Table, Parent Table, Referential Integrity Rules
    Set rel = db.CreateRelation( _
        Name:="FK_Orders_Customers", _
        Table:="Orders", _
        ForeignTable:="Customers", _
        Attributes:=dbRelationUpdateCascade _
    )
    
    ' Map the foreign key field to the parent table's primary key
    rel.Fields.Append rel.CreateField("CustomerID")
    rel.Fields("CustomerID").ForeignName = "CustomerID"
    
    ' Set the join type (choose one):
    ' dbJoinInner = Inner Join (default)
    ' dbJoinLeft = Left Outer Join (all child records + matching parent records)
    ' dbJoinRight = Right Outer Join (all parent records + matching child records)
    rel.JoinType = dbJoinLeft
    
    ' Save the relationship to the database
    db.Relations.Append rel
    
    ' Clean up objects
    Set rel = Nothing
    Set db = Nothing
    
    MsgBox "Relationship created with Left Outer Join!"
End Sub

2. Update an Existing Relationship’s Join Type #

If you already created the relationship via SQL and need to adjust the join type later, use this code:

Sub UpdateRelationshipJoinType()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    
    Set db = CurrentDb()
    
    ' Locate the existing relationship
    On Error Resume Next
    Set rel = db.Relations("FK_Orders_Customers")
    On Error GoTo 0
    
    If Not rel Is Nothing Then
        ' Modify the join type
        rel.JoinType = dbJoinRight
        
        ' To apply changes, you must delete and re-append the relationship
        db.Relations.Delete rel.Name
        db.Relations.Append rel
        
        MsgBox "Relationship join type updated to Right Outer Join!"
    Else
        MsgBox "Specified relationship not found."
    End If
    
    Set rel = Nothing
    Set db = Nothing
End Sub

Key Notes #

• Why SQL Doesn’t Handle Join Types: The ADD CONSTRAINT SQL command in Access only manages foreign key constraints and referential integrity rules (like cascade updates/deletes). It doesn’t expose the JoinType property, which is unique to Access’s relationship object model.
• DAO Reference Check: If you get a "User-defined type not defined" error, enable the DAO library in the VBA editor: Go to Tools > References, then check "Microsoft DAO x.x Object Library".
• Join Type Constants: Use these built-in values for clarity:
  • dbJoinInner (1): Default inner join
  • dbJoinLeft (2): Left outer join
  • dbJoinRight (3): Right outer join

内容的提问来源于stack exchange，提问作者davidzxc574