• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

CreateRelation method

I know that access doesn't support the CreateRelation method on linked tables but I need to dynamically link and set relations from a table currently in the database to the linked table.  I've got the table linked so is there any other way to programatically set the relationship between these two tables?
0
pvega
Asked:
pvega
  • 2
1 Solution
 
peter57rCommented:
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
0
 
pvegaAuthor Commented:
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
0
 
peter57rCommented:
The relationship window only shows what you put there.
Right-click and Show All.

Pete
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now