Database Behavior Resulting from Resetting ID Number

[MichaelA]

I reset the ID number in one of my tables that had data. I did this because I created and deleted over 1,000 practice records trying to set up the database. The reset went fine, it did not impact my data and new values have started numbering from 1. My question is, what will happen when the new numbers catch up to the old records. Will Access skip over the numbers already being used or will it continue numbering in sequence and overwrite existing data.

Thank you!

[June7]

You left records in table and reset autonumber seed? I expect this will not go well. I just tested and Access attempts to create next ID in sequence and if it is already in table, triggers 'duplicate values' error.

However, next time you run Compact & Repair, Access should start numbering from max ID in table. There will be a gap for deleted ID's. So unless you plan to fill in 1000 records before next C&R, you will get gaps anyway. Gaps should be irrelevant to function of this field in db design.

How did you reset the seed? If table had dependent tables, should have used method for related tables described in https://support.microsoft.com/en-us/...alue-in-access

[CJ_London]

what will happen when the new numbers catch up to the old records. Will Access skip over the numbers already being used or will it continue numbering in sequence and overwrite existing data.

Neither, you'll get a database error along the lines of 'unable to insert due to key violations'

If you are going to reset the autonumber, you need to delete all the records, or start the number with a higher value. The autonumber should have no meaning other than to uniquely identify a record, so it should not matter about resetting it

[MichaelA]

I created a backup first in case it did not go well, deleted the relationships, ran the query below.

I reset it with this query:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] COUNTER(1,1);

I will run compact or repair or I could use the query again and reset the number to the next record ID.

[June7]

Should run C&R periodically anyway, especially after design changes.

[MichaelA]

Thank you! I'm extremely new to this.

[Micron]

If you are going to reset the autonumber, you need to delete all the records,

Not sure I would agree with that. IF there are no related records in other tables that need to remain related, I'd remove the index, save and close the table, reopen in design and re-establish the index. Another option would be to import everything into a new db, choosing to import the table definitions only. That will start the seed at 1.