CODEWITHSUNDEEP
ms-accessms-access-2007primary-key
dgBP
dgBP

Reputation: 1693

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

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)?

Upvotes: 2

Views: 15086

Answers (2)

NumNums
NumNums

Reputation: 1

The real problem is that Access is pretty stupid. E.g. if a new table is created and data is entered for the first time but the row is incomplete, the number will still skip when you go back to the table and reenter the data. Even if it was a blank database and there would be no conflicts/error in relationships by doing so. This is particularly frustrating as an instructor because some other behaviours of Access make this very likely to happen.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 3

Related Questions