resetting autonumber

[barrynichols]

I have been developing a db and have been playing around and putting
in data.

Now I need to put in live data, is there a way to reset the autonumber
field back to 1?

Thanks

 

[Dirk Goldgar]

I have been developing a db and have been playing around and putting
in data.

Now I need to put in live data, is there a way to reset the autonumber
field back to 1?

It's not -- or shouldn't be -- important what the value of an autonumber
field is, so there's no real need to reset them. But if you want to do it
for neatness' sake, most versions of Access will reset the autonumber if you
empty the table and then compact the database.

 

[Ken Sheridan]

Compacting and repairing the database will do it, but if sequential numbering
is important then you should not use an autonumber; its designed only to
guarantee uniqueness not sequence, and is consequently not normally exposed
to the user.

If you need sequential numbering then you can do so easily in a single user
environment by looking up the highest number (if any) in the table and adding
1. This is done at form level (in any application worth the name data should
never be entered in raw datasheet view) by putting the following in the
form's BeforeInsert event procedure:

Me[MyID] = Nz(DMax("[MyID]", "[MyTabl]"),0) + 1

In a multi-user environment conflicts could arise with this if two or more
users are adding a new record simultaneously, in which case the first user to
save the record would succeed, but the other(s) would raise an error in view
of the key violation. This scenario can be catered for either by handling
the error in the form's Error event procedure, or the error can be avoided by
allowing only one user to get a new number at any one time. You'll find an
example of the latter technique at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


The demo database available from the above link also allows the number from
which sequencing starts when the next record is added to be reset.

Ken Sheridan
Stafford, England