Resetting AutoNumber

[neillovell]

If I've set a table to have a field ID updated using AutoNumber as its type, how can I reset this to 0? I've created a few test data runs but now I want to reset the number back to zero so new employees are assigned 1, 2, 3... instead of 18, 19, and 20.

 

[KenReay]

Hi

Delete all rows from the table, compact the database

But why bother?, Autonumbers are not meant to have meaning, so who cares at what number they start?

Regards Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[danvlas]

Delete all data in the table and compact the database.
But... if you want to avoid gaps in numbering...don't use AutoNumber, find another way to increment the field...

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[TonyU]

[tt]

Here's how I do it:

1. Right click your table
2. Cut it
3. Paste it (only pasting the structure of the table and not the data).
4. Voila, there u go.

[tt]
^{"The only ideas that will work for you are the ones you put to work."}
[/tt]

​

 

[JeremyNYC]

I see that Tony's post got starred, and I don't want mean to take away from that, but it's actually not a very good way to go about this. Compacting the database is a much better way to do this. If you use Tony's method you will end up with a bloated database file, once you delete that original table. And the only way to deal with this is to compact the database. So you've just wasted your time.

But this is beside the point, because as Ken and Daniel point out, it shouldn't matter at all what your autonumber values are. This is something that a lot of people don't understand. Autonumbers will not be sequential, they will only be unique. This means that you cannot assign any importance to the autonumber. This means if you want something to hold some bit of information, you MUST use a field other than an autonumber field.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.