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! = )
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.