• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

C&R doesn't lower autonumber

hey guys, i read that C&R for access does this

"  Reset the next available number for Autonumber (or “identity”) fields so the next available number is one higher than the current highest value in the table.

if i have a table with an autonumber and i fill some records, and then delete ALL the records, yes after i C&R and add a new record in the empty table, it starts from 1.

however if i have 10 records and then i add 5 more (so now my autonumber for the last record is 15), and then i delete the last 5 records so that autonumber for for the last record now is 10, when i C&R and then add a new record, the autonumber for the new record is 16 not 11.

is something wrong? i tried this in an empty Access 2007 mdb and it's always reproducible. so how come C&R doesn't lower a non-empty table's autonumber field? thanks guys! = )
4 Solutions
Rey Obrero (Capricorn1)Commented:
Did you try it with a .accdb file?

also see this link

Gustav BrockCIOCommented:
I don't know but it is default behaviour.
And as an ID should be meaningless, it doesn't matter.

Jeffrey CoachmanMIS LiasonCommented:
This will depend of what you have done since you added the last record...and what the previous last record was. (it may also depend on the version of Access)
Access does some internal "housekeeping" while the db is running and some when you close the DB, ...this will sometimes "Hold" what it *thinks*  is the last AN.

I have seen some cases where records are added/deleted in recordsets, or SQL, where the starting AN was 10, and the last AN was 20, ...then after deleting rows, and doing C&R, the next AN was 18.

Do you have a specific problem with this functionality?

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

I believe the behavior is different in different versions of Access.  In all cases empty tables seem to get the seed reset but in some versions, tables with existing rows, do not have their seeds reset.

As has already been pointed out, autonumbers are meaningless so don't let it bother you.  If you are using the autonumber for something other than to maintain uniqueness, don't.
developingprogrammerAuthor Commented:
thanks for all your help guys = ) no problems with this functionality just wanted to understand Access more. thanks once again = )
Jeffrey CoachmanMIS LiasonCommented:
<just wanted to understand Access more>
...so do we all


developingprogrammerAuthor Commented:
= ) thanks Jeff!! = ))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now