how to: reset autonum

[zyrag]

hi, how do i reset autonumber field back to zero or to a specified start number?

tia,

 

[dwlerwill]

The only way I know how to reset an autonumber is to delete all the records from the table (Or copy them to another table) and one table is clear compact the database, this will then reset the autonumber.

David Lerwill
"If at first you don't succeed go to the pub"

 

[Zebbeddee]

I would like to know if this is possible as well without doing this. The only way i could get it to work was to create a new filed with exactly the same name make it an autonumber and then delete the old one but there should be a way to reset it without having to do this. It seems rather hap hazard to me.
Is there another way?

Booiinnggg ... Zebbeddee ... Booiinnggg

 

[pbrown2]

Are you wanting to reset the number for report number reasonings? Or is the AutoNumber used for different means?

Thank you for any and all help,

PBrown

 

[wuzzel]

Hi Z,

What David Lerwill suggest works, but it's not the only way. One problem with it is having to rebuild all relationships onto the new table.
Another way would be to make a copy, empty the original, then change the field type twice: once into a normal number(long integer), then back into Autonum (you can only do that on an empty table, as you'll have noticed). Then you can make either a query or a VBA-procedure thats adds the records from the copied table, with new numbers in place of the original autonum-value. This relies on the funny feature that you can add records with autonumber-values of your own choice as long as you explicitly assign a value to that field:
INSERT INTO [tablename] ( autonumfieldname ) SELECT valueyouchoose AS autonumfieldname;

It's a bit of work, especially if you have enforced integrity in your relationships: you'll need to drop the enforcement temporarily and update all related values to the changed values in the source table.
If you need it bad enough, you'll be able to pull it off.

Greetings, good luck, Daan

 

[dongle]

Can I ask you why you want to do this? It is dangerous to use the Autonumber field as a field which is exposed to the user as its integrity must be maintained if the database is to be 100% reliable.