Merging Two databases into one

[Guest]

I have two databases that have the same table structures. both have the same
primary keys id numbers. i.e. from 1 to 12000. The databases are located in
different cities and need to be merged into one. How do I merge them and the
foreign key tables together without losing the existing related recordsets in
the two tables?

 

[John Vinson]

I have two databases that have the same table structures. both have the same
primary keys id numbers. i.e. from 1 to 12000. The databases are located in
different cities and need to be merged into one. How do I merge them and the
foreign key tables together without losing the existing related recordsets in
the two tables?

With great tedium and difficulty, in my experience.

One way would involve the following steps:

- Pick one database as the Source and the other as the Target
- BACK THEM BOTH UP!
- Determine the highest autonumber in the master table in Target
- Open Source, and delete ALL relationships from the master table to
the related tables
(You did BACK THEM UP, right!?)
- Change the datatype of the primary key from Autonumber to Long
Integer (you must delete the relationships to do so)
- Reestablish all the relationships, and check the Cascade Updates
property of the relationship. If there are "grandchild" tables, set
Cascade Updates there too - on *all* instances where the master ID is
used as a link.
- Run an Update query updating the ID's in the master table to their
current value plus a constant large enough that there will be no
overlap with any records in Target.
- Go to lunch and relax while the many updates occur
- Check your data
- Open Target; use File... Get External Data... Link to link to all
the tables in Source (Access will alias them by adding "1" to the
name)
- Back up both databases AGAIN (in a separate location)
- Run a whole bunch of Append queries to append data from the linked
Source tables to the Target tables; these will need to be run in the
correct order to ensure relational integrity isn't violated
- Groan, swear, and complain about the fact that there is data in
Source which causes duplicates or violates validation rules in Target.
Fix these errors. Run append queries again.
- Check the results


John W. Vinson[MVP]

 

[Guest]

OUUCH!!!!

I thought that might be the way I had to do it, I was hoping there was a
utility or another way to do it though code. Is there a way to do this though
code? I am not sure how I could run though all the tables and create an "old"
primary field field. and run this on all "Parent" tables.

Doug

 

[John Vinson]

OUUCH!!!!

I thought that might be the way I had to do it, I was hoping there was a
utility or another way to do it though code. Is there a way to do this though
code? I am not sure how I could run though all the tables and create an "old"
primary field field. and run this on all "Parent" tables.

Doug

I hate to say it... but my suggestions are probably a BEST CASE
scenario. No, this cannot be automated; it requires human
intelligence, familiarity with the structure of the database, lots of
careful work, and BACKUPS, BACKUPS, BACKUPS.

I did not suggest creating an "old" primary field field and have no
idea what you mean.

John W. Vinson[MVP]

 

[Özden Irmak]

Hi,

Give a try to Klik! DataCompareLib :

http://www.kliksoft.com/?S=2&SS=12

Regards,

Özden