View Full Version : Access tables and autonumber

05-07-2002, 12:57 PM
Is it possible to reset an autonumber field in a table so it starts again from 1? If so, how do you do it? Presumably the table would have to be empty before it could be attempted.

05-07-2002, 01:56 PM
nope delete the original coloumn and just reindex it

05-07-2002, 03:07 PM
Yup Mutts is correct, you cant reset the numbers as such. If your table does not contain any records, simply compacting the database again will set the Autonumber field back to 1. Another way would be to delete the AutoNumber field and re-insert it in the table. Of course when you delete records your back to the problem of numbers not being consecutive.

Alternatively you could create your own autonumber which remains consecutive but requires some code to be written. Have a look at http://www.applecore99.com/tbl/tbl012.asp if you are comfortable with VBA.

I would suggest retaining an AutoNum plus creating your own field for numbering depending upon how important it is for you.

05-07-2002, 06:19 PM
Thanks guys, you've confirmed what I suspected, although I didn't know that compacting would reset it. It looks like I might be having to do it the hard way :-(