Making AutoNumber reset to a specified value.

[Jables]

Anybody know how to do this? I'm trying to do it by running an append query to the table that contains the AutoNumber field I want to change. I guess I'm not doing something right. Is there an easier way to accomplish this?

Clay

 

[nickjar2]

I don't think it can be done. This is one reason why I rarely use the autonumber field. I always use code to generate the next max number.
If for example you have
1,2,3,4,5,6,7,17
you could delete record with id 17, compact/repair the database, and the next new record will start at id 8. I think that's all that can be done. I may be wrong though.

Nick

 

[Databaseguy]

Yes, compact and repair will reset the Autonumber. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[JoeMiller]

Check this web page as well:

Tables: Have Autonumber field start from a value different from 1

http://www.mvps.org/access/tables/tbl0005.htm

Joe Miller
joe.miller@flotech.net

 

[Jables]

I discovered that it is possible to do this by using an append query. If you create a temporary table with one field that has the same name of the autonumber field you want to change. You then enter a value of one less than you want autonumber to reset at.

Say I want to change autonumber on Table1, Field, ID to start at 100. I would enter 99 in the temporary table. You then run your append query that you've set up to append temptable's ID value to Table1's ID field.

After that's all done, you delete the new row that the append query added. In the next record you enter, Autonumber will start at 100.

It seems pretty darn absurd to go through all that trouble just to reset Autonumber, but such is the nature of Access, I suppose.

Thanks for all the responses.