Resetting AutoNumber field

[Colin Basterfield]

Hi,

Is there any way of resetting the auto number, as I am testing a database
and filling it up time after time, and the numbers just keep rising after
i've emptied it to start again.

Cheers iA
Colin

 

[Michael Watson]

Yes
Delete all the numbers you don't want
Compress the database (in tools menu I think)
Autonumber should start again.

Mick

 

[Roger Carlson]

While this was true for Access 97, since Access 2000, it does not work. You
have to delete ALL of the records from the table and then Compact the
database (yes, Tools>Database Utilities).

However, you are using Autonumber incorrectly if it matters what the value
is. Neither you nor the user should care what the autonumber value is.
Autonumbers should be used only for primay key values that are used to
create relationships. If you need a sequential numbering system, you should
code it yourself.

On my website (see sig below) is a small sample database called
AutonumberProblem.mdb which illustrates one way to do this.

 

[Colin Basterfield]

Hi,

Thanks for this, no I don't care one jot what the number is, I was just
doing some testing and lazy boy here didn't bother writing a schema
script/program to create the database and I wanted to ship a clean database,
but you have answered my question.

Out of interest I will check out your sample, thanks again
Colin

 

[Colin Basterfield]

Thanks for this.

Yes
Delete all the numbers you don't want
Compress the database (in tools menu I think)
Autonumber should start again.

Mick