Transferring relationships

[Guest]

I have two databases with identical structure and table names/fields, but of
course the data is completely different. In one of these I have set up a
large number of relationships between tables. I want to use exactly the same
relationships in the other database but I can't find a way of importing the
relationships without also importing the data.

Is there a way? Grateful for any assistance.

 

[John Nurick]

Hi Simonc,

I don't think you can import relationships without importing the tables
they relate.

One approach would be to make a copy of the database that contains the
relationships. Delete all the data from all the tables and compact the
database; then import the data from the database that doesn't have
relationships.

(To import the data, either use set up linked tables and then use append
queries, or use the SQO IN clause, e.g.

INSERT INTO MyTable
SELECT * FROM MyTable IN 'C:\Folder\OtherFile.mdb';
)

Alternatively you could fiddle round with VBA code to read items from
one database's Relations collection and append them to the other's. Or
maybe even use SQL to work with the system tables...

 

[Guest]

Thanks for this. I will try your suggestion of making a copy and deleting all
the data, but as my database has around 110 tables what is a quick way of
deleting all the data in all tables?

 

[John Nurick]

I'm not sure. Unless your relationships include cascade deletes, you
have to delete records from the "many" end of each relationship before
you can delete the related records from the "one" end, so you can't just
do

Set dbD = CurrentDB()
For Each T In dbD.TableDefs
dbD.Execute "DELETE * FROM " & T.Name & ";", dbFailOnError
Next

It would be possible to write code that examined the relationships and
ran the queries in the necessary order. Alternatively, you could modify
the above snippet make multiple passes, ignoring errors and noting the
number of records left in the table after each attempted deletion: if
any records are left it will be necessary to try again on the next pass.

But it would probably be just as quick to do it by hand<g>.