Access Auto Numbering

[edieb]

I split a database that was too large in August and archive the excess records. The team continue to do data entry from that time until yesterday. Access went from record number 202034 (last high number record) down to number 76305 (there were 355 records down loaded to create records within the 70000 numbering. Those record numbers are in the archive database. Now Access is not letting new records in because it will cause duplicates. As far as I know the archive database in not linked to the current one. Help this problems stops activity.

[ItsMe]

I don't know where you currently stand with referential integrity. Maybe records have been added after going live.

It may not seem like help but I will try to explain something that might have helped before the "Archive" event. Lrt's take a walk back in time, before this event.

preserve your AutoNumber in a new table.......

If you delete all records in a table with autonumber and then compact and repair your DB, the autonumber will reset to 0.

Let's say the auto number was at 200 before the compact and repair. And you want your autonumber to start at 200 again. Starting with an empty table, much like an imported table without data.........

Identify that record with PK 200 in your "backup" table just so you remember 200 and locate the copy of your original table. You will want to verify 200 later on in the process.

Build an Access query based on your backup table that contains record #200. Move each field into the query by dblclick, starting with the AutoNumber field. Turn this query into an append query. Tell the wizard to Append your empty table that has an autonumber of 0.

Go to the table that just got appended to. Delete ALL of the records and THEN populate the table with the data you want using another Append query (minus the AutoNumber this time.)

Typed this all from memory so backup and then backup again before you start.