Transferring Autonum Table Data

[Kila]

I have been working on a database that has several tables that have relationships via "ID" fields populated using autonum. While I have been working, others have been using a previous version of the database to track data. I would like to transfer the data into the new & inproved database, but I want to maintain the integrity of those ID fields and their relationships with each other. How can I import the old data into the new and improve database without autonum re-numbering my records, AND without losing the ability to autonum in the future?

Thanks for any suggestions.

 

[WayPay]

0. Backup your databases!

1. Make sure that there are no duplicate IDs in the old and new database. If there are, you may have to change those before you import the old data so the values won't conflict when the data is merged.

2. Change the data type of the new ID field from AutoNumber to Long Integer. This should not break the existing relationships, but it might . Check your relationships against your backup afterwards.

3. Append the data from your old database to the new one. Append parent data first (orders before orderlines).

4. Change the data type back to AutoNumber.

There may be problems along the way. Do let us know if you run into them .