Resetting (Autonumber) - Solution

[Brook]

To All:

Found this today and I know that it will be usefull for
me so I thought I would pass it on.

If you have ever set up a database and started testing
had numerous "test" records taking up your autonumber?
Maybe testing a program to assign an Invoice number and
you haven't been able to reset the autonumber to start at
your first invoice number?:

What you do is purge all the records in your database
table that you have used for testing. Then Compact the
database. This will reset your autonumber... If you have
real data in the database, you just purge the dummy data,
then your autonumber will be 1+ the highest value in the
table.

Enjoy

Brook

Enjoy

 

[Jeff Boyce]

An observation...

A scan through this newsgroup (tablesdbdesign) will reveal some consensus
against using the Access Autonumber datatype for anything a user might see.
This is not to say that it cannot (or even should not) be done, just that
there are enough "wrinkles" involved in exposing what is intended to be a
"behind the curtain" unique row identifier to the view of users.

A word of caution -- if the database includes any "child" tables to the
table with AutonumberIDs, re-starting the "parent's" Autonumbers will NOT
update the child tables' use of those IDs as foreign keys. The net result,
in this case, would be you'd "orphan" the child records.

 

[Brook]

I am mearly stating a point that if you have a table that
you are using for testing purposes then this would work so
that you can start your tables clean.

Brook

 

[Jeff Boyce]

Brook

If I've been testing in a new db, I start out "clean" by deleting all rows
and moving forward. No need to "reset autonumber".

Jeff

 

[Brook]

What would you do if you had a primary key that was an
autonumber? And this primary key/auto number field was use
as an invoice number? Would you just start your invoice
number with what ever number was next after all your
testing?

Brook

 

[Alphonse Giambrone]

Brook,

I think you are missing Jeff's point.
If no one sees it, then it makes no difference whether it starts from 1 or
99999.
It sounds like you are using the autonumber for something that the user will
see (Invoice number).
The consensus is against doing that. There are several reasons for that. If
you take his suggestion and search the newsgroup you will see why.

 

[Brook]

Sorry to cause such a fuss... I was just trying to pass
some information along that helped me out...

 

[Lynn Trapp]

Brook, pardon me for jumping in here. I'm pretty sure that my good friend
Jeff would never use an AutoNumber field to create an invoice number for an
application, but let me suggest that there is nothing inherently "evil" (if
I may be permitted to borrow a term from our President) about doing so and
it is certainly the easiest way to do that. However, there is an inherent
problem in using it that way. There is no guarantee that there will not be
gaps in the sequence of numbers. Most business like to have an unbroken
sequence for numbers like invoice numbers and insist on having an unbroken
sequence for numbers like check numbers. A value for an AutoNumber field is
lost once it has been used. Thus, if a user starts creating a record and
then presses the escape key or cancels the creation of the record in some
other way, the AutoNumber generated is gone forever. Therefore, if you are
going to use it for generating your invoice numbers you need to be aware of
that and make your users aware of it.

If you need an unbroken sequence of numbers, then you will have to come up
with another way to generate it.

 

[Jeff Boyce]

Lynn (and Brook)

You captured the gist of my first response -- Autonumbers do a great job,
but I don't like using them for purposes other than they're designed for.

Brook -- if you don't open the topic for discussion, how will you learn what
other folks think? No problems!

Jeff Boyce
<Access MVP>