I have a set of databases each with a table with one-to-many relationships to two other tables.

I want to merge the tables from one database into another, but the primary keys start at 1 for each database - and so the keys conflict when merged into one unified table.

Is there any way I can run a "batch" process in order to change the primary key across all the tables, then merge the tables with the new (and now trully unique) keys?

Perhaps something in VBA would do the job?


Thanks in advance for any help you could give me!

This is actually not too hard to do. And the best thing about it is that it requires only two queries and NO VBA:)
1. Determine the highest key number in the ParentTable that will become the master. Say it is 5286 for example.
2. Create an append query that appends the data from ParentTable2 to ParentTable1. Use an expression as the PK that adds 6000 to the existing value. So recordID 1 of ParentTable2 gets added to ParentTable1 with a value of 6001.
3. Create an append query that appends the data from ChildTable2 to ChildTable1. Use an expression as the FK that adds 6000 to the existing value so that the FKs will continue to match to the correct parent. The Autonumber for the Child table will just increment normally.

This method works because relational databases allow you to add rows to a table and specify the autonumber (or identity) value when you use an append query. As long as you don't duplicate the value of an existing row, you'll be fine. The autonumber's seed will be reset to continue issuing numbers with the new highest PK value.

Thanks so much for the help - this is exactly what I did in the end, kinda annoying to have to move it to a new table to create the new ID's - but much easier than any other way I'd imagined.


Thanks again and sorry for the late reply!

You shouldn't have had to create a new table. One of the existing tables could have become the "master" and data from the other could have been appended.

Glad you got it working though.