By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,714 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,714 IT Pros & Developers. It's quick & easy.

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

P: 22
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.
Mar 24 '08 #1
Share this Question
Share on Google+
3 Replies

P: 5
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.
Mar 25 '08 #2

Expert Mod 15k+
P: 31,754
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.
Mar 26 '08 #3

Expert Mod 15k+
P: 31,754
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.
Mar 26 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.