combining databases

[vicsaccess]

ok, so i'm sure this has been covered but did not see anything. I have several DB's that all run independent of each other just because that was the best way I knew to build them at the time. now, seeing my error, I've created a split DB on a network and i'm trying to convert all the individual DB's into one. they are all exactly the same as for the tables. each DB had 4 tables that are related to each other with all of them having autonumber primary keys, how do I combine them and still have my relationships?

[June7]

With a lot of tedious and careful data transfers because the PK/FK associations will change. Master records will have to be imported into central db with the old PK populated into a normal number field and allow new autonumber PK to be generated. Then import the associated dependent records and run UPDATE sql action to update a new FK field with the new PK by linking on the old PK/FK values. When done, delete the old PK/FK fields.

[Micron]

they are all exactly the same as for the tables.

I don't know what this means.
Are you saying the 4 dbs have the same tables but the data is not the same?
For any given db, every table has an autonumber PK?
Are these record id's just PK's in their own table, or are they FK's in other tables too?
The answers may help anyone who's inclined to comment, but this sort of thing is well documented but the answers are quite lengthy.
Whatever you do, start by backing up each of the four just in case.
Here is one place you might want to start with...
http://www.databasejournal.com/features/msaccess/article.php/2236471/How-to-Merge-Records-from-Two-Identical-Access-Databases.htm

[vicsaccess]

thanks guys, sounds like I have my work cut out for me

[vicsaccess]

ok, I think thru some research and trial I have come up with a method that looks like it will work. take a look at it and let me know if it sounds right and maybe someone else can use it
1.create the new table with the pk set to auto
2.look at the pk's of the tables to be imported and find the one with the lowest number pk.
3.paste dummy information into the new table to get the pk's up to the pk number of the table to paste into the new table.
4.paste the data from the old table into the new table. if you put in the right number of dummy data the pk's should stay the same.
5.delete the dummy data from the new table.
6.move on to the next table to import, set the pk to number.
7.compare the highest primary key from the new table to the old table and figure if you need to add or subtract values to get the pk from the old to meet with the next available pk from the new.
8.use a update query to add or subtract numbers from the pk of the old and execute. keep track of this value incase this pk is also a fk in another table, the same value will have to be changed there also.
9.paste data from the query into the new table. the pk's should match up.
10.continue to the rest of the tables, keeping track of the values you add or subtrack and then do the same to the FK's in the other tables.

not sure if this sounds right or it may be what you guys were trying to tell me but so far its worked

[June7]

PKs will stay same if there were no gaps in the sequence.

Not quite what I suggested but if it gets the job done, fine.