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

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

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:

RelationName
TableLeft
TableRight
TableLeftField
TableRightField

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!
0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

Relations
==============
ID (Autonumber, PK)
RelName
TableName
ForeignTableName

RelationField
==============
ID (AN, PK)
RelID
FieldName


 (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.AddNew
        rst("RelName") = rel.Name
        rst("TableName") = rel.Table
        rst("ForeignTableName") = rel.ForeignTable
        rst.Update

        rst.Move 0, rst.LastModified
        RelID = rst("ID")
        
        For Each fld In rel.Fields
            rstFld.AddNew
            rstFld("RelID") = RelID
            rstFld("FieldName") = fld.Name
            rstFld.Update
        Next fld
    Next rel
End Function

Open in new window

0
 
stephenlecomptejrAuthor Commented:
These both help- I'm sorry wasn't able to attempt to try today.
Will most likely be able to - tomorrow!
0
 
stephenlecomptejrAuthor Commented:
Thanks for the comments they helped!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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