Transferring Relationships

[Steve]

Is there a way to export/import relationships without transferring the actual
data tables? I have made changes to the table relationships and want to
transfer those same changes to live production databases without transferring
any database tables.

Thanks in advance for your assistance,

 

[Allen Browne]

You need to write some code to:
- OpenDatabase your client's mdb,
- Delete from the Relations collection,
- CreateRelation with the fields and attributes you want.

The following example shows how to create the relation programmatically:

--------------------code starts----------------------------
Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")

'Define its properties.
With rel
'Specify the primary table.
.Table = "tblDaoContractor"
'Specify the related table.
.ForeignTable = "tblDaoBooking"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("ContractorID")
'Field name in related table.
fld.ForeignName = "ContractorID"
'Append the field.
.Fields.Append fld

'Repeat for other fields if a multi-field relation.

End With

'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub
--------------------code ends----------------------------

 

[imoux1]

Allen, could you explain your response below? I am only vaguely familiar with
VB.

I am having the same problem as the initial poster - I would like to
preserve only the database structure and not the data (as the data is very
large and exceeds the 2 GB Access project constraint, and is stored on a
network server), but have found no means to do so easily. I cannot link b/c
of fears of overwriting data.

Thanks,
Diana

 

[George Nicholson]

If you want to create a new db with the data structure and relationships of
another db:

-Open Access and create a new, blank database
-File>GetExternalData>Import
-Select the db you want to "copy" from
a) Select all your tables, etc.
b) Click "Options" and under ImportTables select "Definition Only".
Also be sure that "Relationships" is checked.

That should give you a new copy of your old database without the data.
(Be sure to do a compact)

 

[imoux1]

Thanks. I'll try it out.