Hello Everyone,

  I have 2 databases with same structure but diffrent records.

  All i want to do is merge them in single database. Remember that it contains about 25 tables, many of them has autonumber fields as primery key and all of them are related to each-other with cascade update/delete relationship.

  Is there any way to do so? any software or code.

  Thanks in advance.

This can be difficult and require some and work and coordination since the Primary Keys and Foreign Keys can get messed up. There are few automated ways to do this that I know of. Anyone else?

I can share with you some of the manual ways to do this if that is what you decide to do. How large is the database?

Basically use the larger database to recieve data from the smaller database (as _1 tables), then create the queries to append data from the _1 versions of each table to the target tables. You may need extra columns to store the meanginful data from the look up columns, and then do update queries to update the look up columns to the proper look ups based on the static values in the temporary columns.

Not fun.

mmcdonal

Hi

The only auto solution I can think of is to use Replication which typically you would use if you had your laptop "on-the-road" and would then transfer data to another fixed station PC in an Office.
The involves synchronizing and may not be suitable post data entry in a .mdb file
This may not be what you wanted>

It may be that a later version such as office 2007 might do merges such as this. I do have 2007 but have not checked out this aspect. Currently I am familiarising myself with 2007 totally new User Interface. Not easy

MF

Thanks everyone for reply.

i will try you suggetions.

Currently i'm working on suggestion =

step 1 : Take copy of small database and change autonumber fields to long fields

step 2 : update these fields with number greater than those in big database

step 3 : somehow, make big database to allow identity_insert option to true ????

step 4 : use insert into qurey to append data to big database from small database.

hope it will work !!!!

Good Luck

If it is succesfull perhaps you could post a detailed and step by step instruction to the Forum...Something like that I am sure would be of interest to a lot of people

MF