Saving Relationships

  • Thread starter Thread starter Skunk
  • Start date Start date
S

Skunk

During development, I am "transferring" live data to the developing DB. In
doing so I start with an empty database.

How can I save or import the relationships from a database I will delete for
use in that new database?

My DB is split FE/BE. The backend always loses the relationships because I
delete the old, as opposed to the new/fresh DB. No surprise, but what can I
do to avoid recreating the relationships each time?
 
You can import a set of tables, either including their data or as empty
tables, using the File | Get External Data |Import menu item (in Access 2002
at least, it may differ in later versions). The import dialogue includes an
option to include the relationships so you can create the tables and
relationships in your new blank database all in one step in this way.

Or you can do it with some code, e.g. the following is adapted from some
more extensive code for importing objects and would recreate the
relationships from F:\SomeFolder\SomeSubfolder\SomeDatabase.mdb in the
current database if you've already created the same tables in it:

Dim dbsCurrent As DAO.Database, dbsSource As DAO.Database
Dim rel As DAO.Relation, newrel As DAO.Relation
Dim fld As DAO.Field, newfld As DAO.Field
Dim strSourcedb As String
Dim lngCount As Long, lngN As Long
Dim retVal As Variant

strSourcedb = "F:\SomeFolder\SomeSubfolder\SomeDatabase.mdb"
Set dbsSource = OpenDatabase(strSourcedb)

lngCount = dbsSource.Relations.Count
retVal = SysCmd(acSysCmdInitMeter, "Building Relationships", lngCount)
lngN = 1
For Each rel In dbsSource.Relations
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
' Create new Relation object
Set newrel = dbsCurrent.CreateRelation(rel.Name, rel.Table,
rel.ForeignTable)
' Set attributes
newrel.Attributes = rel.Attributes
For Each fld In rel.Fields
' Create field in Relation object.
Set newfld = newrel.CreateField(fld.Name)
' Specify field name in foreign table.
newfld.ForeignName = fld.ForeignName
' Append Field object to Fields collection of Relation object.
newrel.Fields.Append newfld
Next fld
' Append Relation object to Relations collection.
dbsCurrent.Relations.Append newrel
dbsCurrent.Relations.Refresh
Next rel

Ken Sheridan
Stafford, England
 
Thanks, Ken. I follow that (I think). I am aware of the regular import
feature but do not want to do them piecemeal.

The code is what I would like, thus automating.

I will give that a try very soon. Thanks.
 
Ken,

I have attempted the code you supplied -- I cut and pasted!

I get an error 91 "object variable or with block variable not set." I
readily admit not skills when it comes to Access and/or VBA.

The code that is highlighted is:
Set newrel = dbsCurrent.CreateRelation(rel.Name, rel.Table, rel.ForeignTable)

Can you 'splain how to fix it?

Thanks,
 
Back
Top