Merging 2 databases in Access 2007 with auto number in both table

[ramab]

Hi,
im trying to merge 2 databases having the same structure. How do i merge the two databases which have unique identifiers and auto numbers. e.g the first 10 records in table A , it will have auto numbers from 1 to 10 and table B will also have auto numbers from 1 to 10. So will i merged them into 1 table C whereby for e.g table C will have auto numbers from 1 to 20??

Thx in advance.

[RyanT]

Do the autonumbers have any significance? Are they foreign keys for another table? If not, do exactly as you said and insert the records from A and B into C (records 1-10 on table B will now be 11-20). If the keys do have significance, you may have to look into creating duplicate records in one of the tables, then updating the FK on the child records to point to the duplicate records.

[NeoPa]

If they do have significance then they shouldn't be AutoNumbers of course ;)

You can create a series of queries that copy the data across, but any AutoNumber fields should be left out of them (not included in the query or SQL).

Copy / Paste is normally a short-cut, but that can go awry when dealing with tables with AutoNumber fields.

[NeoPa]

Another possible way of getting around this (in case someone has left you with a database with significant AutoNumbers), is to update the design to treat the AutoNumber fields as ordinary numbers; Get the data across normally; Change the fields back to AutoNumber.

Messy, but should work.