How do I reset the autonumber field back to 1 in a table when I a.

[Guest]

After creating and testing database I deleted all records in tables. But now
I would like to have the autonumber field reset back to 1 to start entering
data.

 

[Ken Snell [MVP]]

If the table is empty, then compacting the database will reset the
autonumber back to 1.

Note that an autonumber should not have any inherent meaning and should not
be displayed to a user in most situations. Autonumbers are intended to
provide a (nearly) unique value for records, and can go negative or random.

 

[Douglas J. Steele]

Compact the database.

Of course, it shouldn't really matter what the value of the Autonumber field
is: its only purpose is to provide a (practically guaranteed) unique value
that can be used as a primary key. 143654, 143655, 143656 serves that need
just as well as 1, 2, 3 does. It's unusual to show the value of the
Autonumber field to your users, since it has no meaning.

 

[John]

Hi guys,
I'm Curious.
Why shouldn't the Autonumber be shown or even used as a
Client ID for example.
Instead of me having to issue a uniqe Id for a particular
set of items, why not let the Autonumber do it.
Are there pitfals to this?

Thanks
Brad

 

[Ken Snell [MVP]]

So long as you don't mind that your ClientIDs will have gaps and not
necessarily always be postive, etc., then there is no problem using
autonumber as a ClientID.

What usually occurs, though, is someone begins to use autonumber as a
sequential numbering device, and then decides that all numbers must be
consecutive -- no gaps allowed. And then the person decides he/she wants to
change a number for an entity, and wants to know how to reassign all the
numbers (which should not be done in any situation involving the primary
key, regardless of whether you use an autonumber or not as the identifier).

It's possible to use the autonumber as the "hidden" primary key and then to
generate (even automatically) a visible ClientID that can be enforced to be
consecutive, with no gaps, and even could be "changed" if desired -- all
without affecting the very important primary key value.

So our initial recommendation is always to not make the autonumber the
visible value IF that value is to have "value" (sorry, couldn't resist the
word play!) to the user. But it can be used for this purpose so long as the
database developer and the user understand its inherent capabilities.

 

[John]

Thanks for the clarification Ken.
Much appreciated!

 

[Guest]

What usually occurs, though, is someone begins to use autonumber as a
sequential numbering device, and then decides that all numbers must be
consecutive -- no gaps allowed. And then the person decides he/she wants to
change a number for an entity, and wants to know how to reassign all the
numbers (which should not be done in any situation involving the primary
key, regardless of whether you use an autonumber or not as the identifier).

It's possible to use the autonumber as the "hidden" primary key and then to
generate (even automatically) a visible ClientID that can be enforced to be
consecutive, with no gaps, and even could be "changed" if desired -- all
without affecting the very important primary key value.

Where can I read something about how to generate such a visible ClientID?

 

[Ken Snell [MVP]]

Try this Google search:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&q=increment+numbers+microsoft+access&btnG=Search

And this Microsoft Knowledge Base article search:
http://support.microsoft.com/search/default.aspx?catalog=LCID=1033&query=increment+number+access

--

Ken Snell
<MS ACCESS MVP>