Need VBA script to loop through all relationships and their field or foreign key names to insert values into a table?

Posted on 2011-10-14
Last Modified: 2013-11-28
I would like to loop through all relationships each table has and record all that information in a separate table (called RecordRelations) that would have the following fields:


And as an example for the following:

Set oRel = CurrentDb.CreateRelation("TableATableB", "TableA", "TableB", dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
  oRel.Fields.Append oRel.CreateField("TableIDA")
  oRel.Fields("Room_Number").ForeignName = "TableIDA"
  CurrentDb.Relations.Append oRel

I would have the information stored:

RelationName            <---TableATableB
TableLeft            <---TableA      
TableRight            <---TableB            
TableLeftField            <---TableIDA
TableRightField            <---TableIDA

But the above sample code shows how to add a relationship - what I want to do is store the relationship values in a table (that it already has), break the relationships by importing such via VBA and then later reattach by looping through the RecordRelations table and using the values stored there.

I have VBA code for already taking a SQL statement and INSERT INTO [RecordRelations] - so please don't spend a lot of time on that aspect of it if you don't have the time.  I just need the looping part of going through the relationships and at least storing each part above in a string.

Thanks for in advance!
Question by:stephenlecomptejr
    LVL 119

    Assisted Solution

    by:Rey Obrero
    LVL 84

    Accepted Solution

    FWIW, Access already stores this information in MSysRelationships, if you can use that. The column names are a bit cryptic:

    szColumn: The local field
    szObject: The Local table
    szReferenceColumn: the Remote field
    szReferenceObject: The remote Table
    szRelationship: The "name" of the relationship

    The grbit column stores all the attribute info about the relationship (i.e. dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade). If you need to parse this, you can use bitwise logic to handle this, but it doesn't appear you need this information.

    Access also exposes the Relations collection, which can be used to do this. The code snippet below will loop through all of the items in the Relations collection and store them in two tables (see below). You could change the code below to work with your desired structure, if you're not able to use MSysRelationship:

    ID (Autonumber, PK)

    ID (AN, PK)

     (Relations and RelationFields), which are
    Dim rel    As DAO.Relation
        Dim rels   As DAO.Relations
        Dim fld    As DAO.Field
        Dim rst    As DAO.Recordset
        Dim rstFld As DAO.Recordset
        Dim RelID  As Long
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Relations WHERE 1=0")
        Set rstFld = CurrentDb.OpenRecordset("SELECT * FROM RelationFields WHERE 1=0")
        For Each rel In CurrentDb.Relations
            rst("RelName") = rel.Name
            rst("TableName") = rel.Table
            rst("ForeignTableName") = rel.ForeignTable
            rst.Move 0, rst.LastModified
            RelID = rst("ID")
            For Each fld In rel.Fields
                rstFld("RelID") = RelID
                rstFld("FieldName") = fld.Name
            Next fld
        Next rel
    End Function

    Open in new window

    LVL 1

    Author Comment

    These both help- I'm sorry wasn't able to attempt to try today.
    Will most likely be able to - tomorrow!
    LVL 1

    Author Closing Comment

    Thanks for the comments they helped!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now