Importing/Exporting Relationships

Question

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:

1. Drop the all the relationships the main table has
2. Change the main table
3. 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.

Answer 1

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.