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
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("Room_Number").ForeignName = "TableIDA"
I would have the information stored:
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!