Can I Re-set an auto number field ?

[GeoffT]

Have copied one of my databases, re-named it and deleted all records.
I want it for another purpose but in terms of fields, design and reports it is just what I need.

However - I have an auto field - record number - this obviously remains at the number of the last record in the (deleted) database.

Can I re-set the numerical origin of this field to start again at one ? Or do I have to delete it and set up another auto record number field ?

This is not an urgent enquiry - probably a bit basic for many on the forum - but just to add to my knowledge of Access.

Regards.
Muggle

 

[bob fitz]

I think it will be reset if you use Compact and Repair, but as that kind of field should only be used as a unique identifier for each record, what does it matter if it starts at 1 or 1002 or whatever? The numbers generated are not necessarily consecutive.

 

[ypma]

As pointed out the numbers generated should not be relied on as sequence of conscutive numbers . Having said that, there are a number of ways to reset the sequence. If you do not have any records, delete the auto number field from the table and after saving the deletion, re insert it . If that fails you could use a Data Definition query and i could provide you with an example if you inform me of the Table name and the Auto number field .name

If you are using the auto number as a client id there of many examples on this forum
as how that could be acheived without using auto numbering

 

[boblarson]

Just deleting all records from the table and them running Compact and Repair will reset it. But as noted, if your number has meaning then you are using the auto number incorrectly. It only guarantees a UNIQUE number, not a number in a specific sequence or even a positive number.

 

[boblarson]

The autonumbers are not reset by compact and repair in A2010.

Really? Because I just did it two minutes ago in 2010 with both a 2007-2010 and 2002-2003 file format.