Re-ordering Autonumbers

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

The subject just about says it.

If I should delete records, in this case about 85% of
them, from a table of course the now current "first"
record has an autonumber that is not "1".

I believe I recall that I have to put these records into
another table to get the numbering re-ordered from "1".

Is that correct?

Brooklyn "Dave" Guy

(Please respond to this newsgroup vice my own e-mail
address so that others may see what you answer and
possibly embelish on that or learn from it.)
 
That's one way to do it - dump them to a new table. Delete everything from
the old then Compact the db. Then append everything from the temp table
except the AutoNum field.

Of course this will not work if you have relationships to other tables built
on this field. If this is the case then you have to drop all of the
relationships before you can delete all the records. Then after appending
the data back in you have to update all of the related records in related
tables to use the new autonum value. Finally you can re-establish the
relationships on the table. Depending on the complexity of your data this
can be a real pain and fraught with errors.

The real question is why do you want/need to do this? If the number value is
important then you might want to rethink your use of the autonumber field
and consider using a custom counter instead. (Google for "Custom Counter"
for several ideas on how to implement or ask here).

If this is a one time fix then go for it.
 
Actually Sandra there is no *logical* reason to do so.
Just your ordinary obessive coder there.

Anyway, thanks for the answer and have a good one.

Dave
 
Actually Sandra there is no *logical* reason to do so.
Just your ordinary obessive coder there.

Anyway, thanks for the answer and have a good one.

Dave

*** snipped ***


Your going to get missing numbers every time you delete a record, so
just forget about it. The purpose of autonumber is to assure correct
parent-child relationships. Fiddle with the numbers and you'll lose
those relations (I wish I could lose some of my relations that
easily). :-)

The easiest way to resolve your "obsessive" code problem is to simply
not display the autonumber field on your form. Then, whether the
numbers start at 1 or 9876, it wouldn't matter to you. ;-)
 
Back
Top