Is there an easy way to reset the autonumber field to 1 in Access

[Guest]

Is there an easy way to reset the autonumber field to 1 in Access 2003? I am
using a copy of another MDB and need to reset the Issue ID (autonumber field)
to start at 1. The instructions from MS seem convoluted and it seems like
there should be an easier way than appending another table.

 

[Danny J. Lesandrini]

No, but why would you want to? The autonumber field should
be meaningless to the user, if you even choose to show it at all.

The hard way to reset it to 1 is to delete all rows from the table
and compact the database.

 

[Joseph Meehan]

Is there an easy way to reset the autonumber field to 1 in Access
2003? I am using a copy of another MDB and need to reset the Issue
ID (autonumber field) to start at 1. The instructions from MS seem
convoluted and it seems like there should be an easier way than
appending another table.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

Deleting all current records and compacting the database should do as
you want.

 

[Larry Daugherty]

The Autonumber datatype exists to generate unique long integers to
serve as surrogate Primary Keys. They are not guaranteed to be
gapless or sequential. Those autonumber Primary Keys should never be
shown to the user. Notice that MS exposes them in all of its demo's.
That's one of their more egregious sins where Access is concerned.

If you need to generate a sequence for some purpose other than Primary
Keys then you write the code to generate and manage your sequence.

Microsoft and many helpful developers here will help people to twiddle
and tweak their autonumber field contents into some result that
inherently has nothing to do with RDBMS functioning. That means going
through some amount of work to achieve a pretty picture that adds no
value to your application. That work costs something in terms of
opportunity costs and probably in terms of the money your employer
pays you.

Another kicker is that what ever got you into the current situation
will likely happen again.

HTH

 

[Guest]

While some may argue that the autonumber field is for key-linking and not for
informational purposes, I can argue that it is still useful information to
determine what was the first entry, what was the last entry, and what were
the most recent entries, etc. The autonumber field can really be useful
information BESIDES the one-dimensional view that it is only a key field.

To resequence it from the start, delete the field, then re-create it and the
numbers will be in sequence.

 

[Douglas J. Steele]

If you need that sort of information, put a CreateDtm Date field in your
table, and set its default to Now().

Recognize that if you have need to apply replication to your database, the
autonumbers will become random, rather than sequential, so it's dangerous to
attach any meaning to their values.

 

[Ron2005]

Keep in mind in all of this that

If the system is using that autonumer as a foreign key in another
table, if you resequence it then all of the subordinate files no longer
will have matching information........