How to reset the autonumber field?

[Migs]

I tried to compact the database to reset the autonumber but it seems that the
record was not reset. When I add a new entry, the number assigned is still
the increment of the last deleted record. Is there any workaround for this
issue. Can I change the field to Number and then manually add the lacking
number and then change it back to Autonumber? Will the autonumbering
increment from the last record? I'm afraid the database will be corrupted
but I'll be doing a backup anyway. Please advise.

Thanks,
Migs

 

[Allen Browne]

If you have deleted all records and then compacted, it should reset to 1.

If that does not happen and you are using Access 2000 or 2002, you need to
download the service pack for JET 4. It's available at:
http://support.microsoft.com/kb/239114

It is also possible to programatically set the Seed property of the Column
in the Table of the Catalog using ADOX, but the JET service pack is
important to fix other problems also.

 

[Tom Wickerath]

This is normal behavior with the later JET service packs. The only way to reset the autonumber to
1 is to delete all records from the table, and then do a compact and repair.

If you are trying to use the autonumber as a count of records, don't. It is not designed for this
purpose. An autonumber is a meaningless long integer, which, when set as a primary key, provides
a mechanism to uniquely identify a record.

Can I change the field to Number and then manually add
the lacking number and then change it back to Autonumber?

No. You'd have to delete the autonumber field and then re-create it. If a relationship was
present to a child table, you'd have to delete the relationship first before deleting the
autonumber field. However, any related records in child tables would now likely end up with the
wrong parent record.

Tom
_______________________________


I tried to compact the database to reset the autonumber but it seems that the
record was not reset. When I add a new entry, the number assigned is still
the increment of the last deleted record. Is there any workaround for this
issue. Can I change the field to Number and then manually add the lacking
number and then change it back to Autonumber? Will the autonumbering
increment from the last record? I'm afraid the database will be corrupted
but I'll be doing a backup anyway. Please advise.

Thanks,
Migs