Question Locked

How to renumber record ID in Access

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm looking for what should be a simple thing (at least I thought it was). How do you renumber the record ID field?
It is an auto number field. After deleting records, we have non-continuous numbering which is not helpful for the intended application.
How can I renumber the record ID?

7 replies

by shellbot

you really want to do this?

Are there any relations to other tables which may be affected etc??

Not sure if there's another way, but this is how I'd do it if I wanted to (which I never would...its a unique number for a reason)

Create a copy of the table structure and then import all the fields EXCEPT the autonumber field from the old table to the new table.
Access will auto generate the new autonumber for you.

Once you've moved all the records over using, you rename the old table and then give the new table the old table's name.

At least you've got a copy of the original if something breaks.

by tony hopkinson

you shouldn't be using autonumber. That only appears to work if you start at 1, increment by 1 and never delete.

Shell's solution will get you back to tp the appearance of what you want, but your solution is flawed compared to your requirements.

There are a few programatic (and problematic) ways to get what you say you want), but personally if I wanted to explain where the 'missing' numbers had gone, I'd do somethike add an extra column on the table to carry a deleted flag (and set that instead of deleting) then change my query to not select them.

HtHs

P.S. if you are using Record_ID as an indicator of the position in the table, you are going to run into a LOT more problems later on, because it's not.

by shellbot

Which was why I asked if the poster was really sure thats what he wanted..

My solution is something I would never do, and I never delete from a database (just flag for delete) so I've never had to use my solution :)

by tony hopkinson

I've watched a lot of people shoot themselves in the foot with them.

Their only purpose is to uniquely identify a record. Any other use is a screw up waiting to happen.

by shellbot

I've seen them used for really "interesting" things..

Someone with just enough knowledge to create something can be a dangerous person.

I've been asked to do some "interesting" things with databases..I refuse..to create or "fix".. on the basis that anything i touch is going to make a dogs dinner of it all!

by balmaiden

If you are sure that you really want to do this!!! This works with Access 2010.

1) Delete all empty records
2) View Table in Design View
3) Right click the Autonumber field
4) Delete the field, confirming delete of Primary Key when asked
5) Insert Row
6) Set new row to Autonumber and name as before eg Counter

Job done. - Beware, this change is irreversible. Porbably a good idea to keep back up copy of original database before doing this.

This discussion is closed to new replies.