Autonumber primary key

[Annie]

Hi I have a database that I have cleared all data from and want to start
using the same design with new 2009 records but my autonumber (obviously) has
started at the last number used so 104. Is it possible to change this back
to number 1.

Thanks

 

[Ken Sheridan]

Compacting and repairing the database will reset the autonumber, but
if sequential numbering is important then you should not rely on an
autonumber, but use a straightforward long integer number data type
and compute the next number when inserting a new record.

This is commonly done by putting code along theses line in a form's
BeforeInsert event procedure:

MyID = Nz(DMax("MyID","MyTable"),0)+1

This is fine in a single user environment, but can cause a conflict in
a multi-user environment on a network if more than one user is adding
a new record simultaneously, in which case all but the first to save
the record would get an error resulting from the key violation.

Roger Carlson has a simple way of handling this at:

http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb

Or there is my more complex solution at the following link, which also
includes provision for resetting the number at which the sequence
restarts when the next new record is inserted:

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

Ken Sheridan
Stafford, England

 

[a a r o n . k e m p f]

In SQL Server, I can use the TRUNCATE statement-- this will reset the
autonumber count for that table-- without requiring me to kick out all
the other users in the database

 

[Gina Whipp]

Annie,

With the database empty just run a Compact and Repair located under Tools on
the Menu.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Aaron Kempf knows nothing]

In jail, I can use the TRUNCATE my backside statement-- this will set the
table with applesauce-- without requiring me to get a kick out of all
the other users in the jail