Autonumber Field

[VaughnIT]

I had a table with 13 records and manually deleted the 10th record from the table. The first field PersID is of type Autonumber. How can I get that be reordered so the PersIDs are numbered from 1 to 12 consecutively rather than having PersID 10 missing.
I need this because right now the database has all dummy test data... but when I'm done I'd like the user to be able to load their own data and be able to have the PersIDs loaded and numbered consecutively starting at 1.
Can anyone explain how to get that done and if this requires some VBA code please elaborate on what it is and where it goes.

Thanks in advance

 

[TheAceMan1]

How are ya VaughnIT . . . . .

You've touched on one of the toughest problems in DB design. Autonumber was meant to simply be [blue]an Unique Identifier[/blue], no,matter how many gaps. In fact, if you ever compact, the next autonumber is always reset to the highest autonumber in the tables (per table) +1.

Too make things easy on yourself ([purple]since autonumbers can't readily be changed without too much extra coding[/purple]), I would leave it and add an additional ID, this of which could be easily serialized with code. You would maintain uniqueness with autonumber ID, and have max versatility throughout the DB with the additional ID. Besides, changing sequence of PK of a cascading relationship can prove to be a nightmare! (believe me . . . . . I've been through it too many times). The new ID, allows you to have that Bud!

Its a good Idea for every programmer to come up with their own [blue]AutoNumber Generator[/blue]. This too, could be easily serialized since its updated through code. Once done, you will of course incorporate itn every DB thereafter.

I'd really rather you had that Bud!

See Ya! . . . . . .

 

[willir]

VaughnIT

The purpose of the autonumber is to uniquely identify a record. It is not intended to to be used as a "counter" for the number of records in a table.

I know, I know, "but I want to ..." There are other posts on this issue. The sequential nature of the autonumber creates a strong desire to assoicate the sequential number as a record counter -- this is purely cosmetic.

If you want to have a true sequential number for your employees, you would have to create your own.

Here are two reasons why autonumbenr will fail as a "counter"...
- After creating a number of records, you realize that you have a couple of duplicate entries. You decide you have to delete record #2. Does this mean that you have to re-number all the higher numbered records. Then you have to re-number all the links to the attached tables. Quite do-able with a few hundred records - what about a few thousand, or 100,000? A lot of of work - especially for something that is cosmetic.
- The "missing" autonumber. Start creating a new record -- note that the autonumber is generated, say 110 for this example. Hit the ESC key - the autonumber disappears, and the word "autonumber" appears in the field. Start entering a new record - the system assigns the next number, 111. 110 is gone! But this is just cosmetic - from the perspective of the system, it can still identify the record you arecreating, and the link to any applicable tables will be created.

Richard