CreateRelation method

Hello pvega,

> I know that access doesn't support the CreateRelation method on linked tables
?Who told you that then?(:-)

You can't enforce referential integrity with linked tables but that's all


This is based on code I pinched from the net sometime ago - I don't know the original author.

Sub CreateRelationDAO()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    'Initialize
    Set db = CurrentDb()

    'Create a new relation.
    Set rel = db.CreateRelation("EmployeePO")

    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "Employees"  '******* in this database
        'Specify the related table.
        .ForeignTable = "Purchase Orders" ' ********linked table
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationDontEnforce

        'Add the fields to the relation.
        'Field name in primary table.
        Set fld = .CreateField("EmployeeID")
        'Field name in related table.
        fld.ForeignName = "employeeID"
        'Append the field.
        .Fields.Append fld

        'Repeat for other fields if a multi-field relation.

    End With

    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel

    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
    msgbox "Relation created."
End Sub


Pete

Hey Pete,

Modified and ran the code with no errors but I don't see a relationship between the tables.  I'm looking in relationships window.  Thanks.

Pam