How to reset autonumber in access 2002/Xp

[greatmacoy]

Hi,

I've been using access for years already. In MS Access
2000, if you deleted the last record in a table and you
want to reset the autonumber to the next value (from 10,
followed by 11 and not 12 or any other value, all you have
to do is to compact the database.

I tried doing the same with a MS Access Xp database but it
din't work. Why? How can I reset the value?

 

[Ken Snell]

For this version of ACCESS, only way I know is to copy all the data to a
temporary table, delete the data from the original table, compact the
database, and then append the data from the temporary table back into the
original table.

 

[greatmacoy]

Thanks Ken,

So, with MS Access Xp, the autonumber field will not reset
like the earlier versions if the table has data in it.

greatmacoy

 

[Jeff Boyce]

If any other (child) tables depend on entries in the table you are trying to
reset for a foreign key, they will lose their (correct) connection when you
reset/renumber.

Autonumbers are great unique row identifiers, but as such, rarely need to be
seen. Why do you care what value the autonumber field holds?

 

[Ken Snell]

That is correct. Of course, as Jeff asks, why should this be necessary to
do? Autonumbers should not be exposed to the user in any way, so the actual
values should be immaterial.