Changing IDs in a table for data migration

[FriQenstein]

Greetings all,

I have a DB that I built and am still using. I recently redesigned it from the ground up due to a variety of necessities.
The new version I have built has been done on the side, so a completely separate Access DB file.

The existing working data from the current used DB will eventually need to be imported into the newly redesigned DB. However, while building the new version, I have already imported a copy of the original data, so that I could work with it while building/testing.

Question: When I am ready to rollover to the new DB version, I will need to re-import all of the working data once again due to updates. When I do this, I will be deleting all of the previously imported records and importing the recent data. During this, I would like to be able to reset the table ID numbers, or perhaps have them start from a specific number. However, because data has already been imported once, it will likely pick up form the last ID already in the table.

Is there a way to reset the IDs prior to, during, or after the new data is imported?



Regards.

[ArviLaanemets]

Before importing new data, clear all involved tables, and compact database to reset autonumeric keys (this resets them at lowest possible value, so empty tables will continue at 1);
For all involved source tables (tables with autonumeric Primary Key), add a Long Integer field, where old Primary Key value will be stored;
Import data from old database, storing for tables with autonumeric PK the value of old PK into added field;
Update all Foreign Key fields in tables which must be linked to PK's of other imported tables. Update queries must replace old key value with new PK value in source table - using added field to select proper row from source table;
Delete added fields from all tables. It's done!

[FriQenstein]

Thank you for the response. I will give this a try on a backup copy and see how it goes.

Regards.

[FriQenstein]

@ArviLaanemets,

Thanks again. It seems to have worked.

Regards.