Updating Multiple Related Tables

[generallyconfused]

I have 2 databases that have five data tables that are related to each other. The relationships look something like this....

Database 1 (db1) Database 2 (db2)
Tbl2 Tbl2
/ | \ / | Tbl1-+-Tbl3--Tbl5 Tbl1-+-Tbl3--Tbl5
\ | / \ | /
Tbl4 Tbl4

Unfortunately, this turns into quite a wicked little web when I attempt to merge these two databases together.

First I have to go through db2 and renumber to ensure no conflicts exist with entries in the db1 tables. My problem arises when I go to addnew a record from db2.Tbl1 to db1.Tbl1. The entry from db2.Tbl1 refers to db2.Tbl2, which is a key that has not yet been created in db1.Tbl2. So this is my dilema.

I think there might be 2 ways around this, but I'm putting them to you for your opinion.

1. Somehow suppress or deactivate the error message or the constraint on the tables until the merge is complete. I could delete all constraints, merge, and recreate all constraints, BUT I'm hoping there is an easier way.

2. Can I use an UPDATE or INSERT query, joining all five tables from db2 and adding it to a join of all five tables in db1?

I thank all who reply in advance.

I have become significantly balder since I began the process of creating a database merge tool, but the end is near, I hope.

 

[BNPMike]

I've never used relationships (which is I guess what you are talking about) but even so I can't see why you are having a problem. We can't see the direction of your links but let's assume Tbl2 has Tbl1 as 1:n and Tbl1 has a foreign key to Tbl1.

As long as db1 accepts the data then you should be able to insert it into db2. In this case load all the new Tbl2 records. Then add all the corresponding Tbl1 records. Use this principle to work through the database. You could do more than one table at at time but why bother.

If you are using the QBE grid to build your queries then just delete any tables Access tries to add and delete any join lines you don't want.

If you have referential integrity activated then all you need to remember is that when you insert a record into a table the relevent foreign keys must be present in the referenced table or be NULL. Just do them in the right order and you won't have a problem.