Append Records in one-to-many table to another one-to-many table

[MeisoT]

The database version I am using is Access 2000. I have two tables that have a primary key set to Autonumber. These tables are populated with data.

Problem- I need to append records that were entered into an offsite database to the tables mentioned above. The offsite database has the same exact structure as the other one. When the records are appended, I need to replace the autonumber to begin at the next consecutive number and have the foreign keys update with the new autonumbers so that the relationships are not broken when the records are appended.

Does anyone know how to accomplish this without using replication.

Thanks.

 

[lupins46]

Backup both databases first.

In the second database, change the autonumbers to numbers.
Run an update query on all key fields (primary and foreign keys) to add a fixed number to evry value; add a number which ensures that you do not have duplicate keys between your two databases.
Then append the records form the second database to the first, including the key field.

 

[MeisoT]

Thanks alot for your suggestion lupins46. I think it will work, but I won't be able to test it out today as I have a deadline to meet on a project.