AutoNumber - delete records

[Michelle]

Hi,

I have made changes to a database, in the meantime, users
have added data to the old version. I copied the new data
into the new version however I was not able to use the
same ID numbers. I had to use the next ones on the list.

Also, I have about 10 empty records and I would like to
delete them, then have the id auto number start after the
last record with data in it.

Is there a way to fix this?

 

[kevin]

Michelle,

I know that this is a pain. Access does not have a simple
fix to re-align if the id is an autonumber. One way that
i have done this is to use a query to create a new table
and this allows to start the autonumber over, but this is
not easily done. Plus you have to re-establish all the
links to that table. As for deleting entries you can but
there is no way to easily get those numbers back if they
are auto numbers. The only other option is to use a
different field for autonumber and PK then manually enter
the ID. the problem that this produces is that you could
accidentally duplicate the ID number.

 

[John Vinson]

Hi,

I have made changes to a database, in the meantime, users
have added data to the old version. I copied the new data
into the new version however I was not able to use the
same ID numbers. I had to use the next ones on the list.

Also, I have about 10 empty records and I would like to
delete them, then have the id auto number start after the
last record with data in it.

Is there a way to fix this?

This is exactly how autonumbers are designed to work. They are NOT
guaranteed to be gapless, or even sequential; the *only* function of
an autonumber is a guaranteed (almost, there are bugs occasionally)
unique key. If the value of the autonumber is of importance to you, or
if you will be exposing it to users... don't use Autonumber! Instead,
use a Long Integer and maintain its value with code.

I use autonumbers often but conceal them from user view when I do.