How reset autonum back to 1 ?

M

mscertified

I have emptied my table and want the autonumber key to recommence at 1. This
column is a part of many relationships so I cannot delete the column. Is
there an easy way? Thanks.
 
J

Jeff Boyce

If this table is "a part of many relationships", won't deleting records from
this table leave "orphans" in those related tables? I am assuming you have
used the autonumber primary key in this table as your "foreign key" in the
related tables.

This is just one reason not to try resetting an autonumber to 1.

For the record, as I understand it, the Access Autonumber data type is
intended to serve as a unique, arbitrary row identifier. It has no meaning,
and is generally unfit for human consumption (unless the human understands
the limitations/constraints associated with the Autonumber).

Why? (as in "why do you want to reset it to 1?)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

KARL DEWEY

Do a Compact and Repair.

If that does not do it then append a phony record with 0 (zero) to that
field and then delete the record.
 
K

Ken Sheridan

If the autonumber column serves merely as a set of unique values with no
intrinsic meaning, which as Jeff pointed out, is what its designed for, then
there is no point in resetting its value. If a sequential number is really
necessary you can download my demo of how to generate such numbers in a
multi-user environment, with a facility for resetting the start number for
the next sequence at a number of your choice from:


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


Ken Sheridan
Stafford, England
 
S

Susie

Why? Because client has jobs numbered in an Excel spreadsheet and they need
to match. This is a new database, and in all my experimenting I am up to 149
when the real job ID is 1. I tried to delete the row and create a new
autonumber primary key to see if it would reset, but it was too smart for me
and started where the old row left off. I convinced the client that this was
of no real consequence, but it would be nice to be able to do it.
 
S

Susie

Sorry, too new to understand the language. Compact & repair? Append a phony
record or any record for that matter? Need how-to help. Please.....
 
D

Douglas J. Steele

You may be in for a very unpleasant surprise, then.

Autonumbers are NOT guaranteed to be consecutive: gaps in the numbering will
occur if you start to enter a record then change your mind partway through.
 
J

Jeff Boyce

Susie

If there's an Excel spreadsheet that holds the "good" ID number, why use an
Autonumber in Access? Instead, use an Integer or a Long Number field and
just type in the number from Excel.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

Susie

Hi Jeff

Client asked that number be autoassigned moving forward. But, as I said I
have already convinced them that this was not really a matter of concern.
Hopefully, they will not have to use Excel once they can enter data live into
Access. That's what they're after.

Thank you for your interest.
 
Top