CreateRelation method

Posted on 2004-11-19
Last Modified: 2012-05-05
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?
Question by:pvega
    LVL 77

    Expert Comment

    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

        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


    Author Comment

    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.

    LVL 77

    Accepted Solution

    The relationship window only shows what you put there.
    Right-click and Show All.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now