Id field (primary key) is skipping numbers of rows that have been deleted, how to change?

Question

I'm using MS Access and have created a simple table. I have one column as the standard ID primary key (renamed to Number). I deleted a selection of rows, but now when I go to the next row, the Number column counts from the deleted numbers.

E.g. it looks like:

Number Name
1       etc
2       etc
3       etc
6       etc
7       etc
8       etc

Where rows 4 & 5 have been deleted.

I removed all the rows that came after the problem (i.e. 6,7,8 in this case) but then it starts from 9. Is there any way I can start the count back at 4 (as I have rows 1,2,3 left)?

Solution

That is what an autonumber is supposed to so. If you need a counter that means something, you should not use an autonumber.

Access is a relational database, if you could delete a row and then add a new row with the same number, you would throw the relationships out of kilter.

If you need a sequential number see Access VBA: Find max number in column and add 1

If this is a once-off problem, you can delete the current autonumber field from the table and save, then add the autonumber again, but it would be much better to forget about a sequential autonumber. Autonumber should never be shown to the user. It can never be relied upon to be anything but unique, and if you mess about enough, not even that.