Importing/Exporting Relationships
ms-access, table-relationships, vba
Solution
Not a complete solution, but this may get you going...
The following function will print out the metadata for all relationships. Change this to save to a file in whatever format you prefer (CSV, tab delimited, XML, etc.):
Function PrintRelationships()
For Each rel In CurrentDb.Relations
With rel
Debug.Print "Name: " & .Name
Debug.Print "Attributes: " & .Attributes
Debug.Print "Table: " & .Table
Debug.Print "ForeignTable: " & .ForeignTable
Debug.Print "Fields:"
For Each fld In .Fields
Debug.Print "Field: " & fld.Name
Next
End With
Next
End Function
This function will drop all the relationships in the database:
Function DropRelationships()
With CurrentDb
For Each rel In .Relations
.Relations.Delete Name:=rel.Name
Next
End With
End Function
This function will create a relationship. You'll have to iterate over the file of saved relationship data.
Function CreateRelationships()
With CurrentDb
Set rel = .CreateRelation(Name:="[rel.Name]", Table:="[rel.Table]", ForeignTable:="[rel.FireignTable]", Attributes:=[rel.Attributes])
rel.Fields.Append rel.CreateField("[fld.Name for relation]")
rel.Fields("[fld.Name for relation]").ForeignName = "[fld.Name for relation]"
.Relations.Append rel
End With
End Function
Error handling and IO omitted due to time constraints (gotta put the kids to bed).
Hope this helps.
Problem
I have a couple of mdb files with the exact table structure. I have to change the primary key of the main table from autonumber to number in all of them, which means I have to: - Drop the all the relationships the main table has - Change the main table - Create the relationships again,... for all the tables. Is there any way to export the relationships from one file and importing them to all the rest? I am sure this can be done with some macro/vb code. Does anyone has an example I could use? Thanks.