Re-numbering Primary Key

[Victor]

I am using Access 2002 and have a data base with over 1000 records
with a field as auto number as the primary key. I want to delete some
records and re-number them so the numbers run consecqutive and the
records stay the order they are in. Is this possible? All help is
greatly appreciated.

Vic

 

[Van T. Dinh]

AutoNumber will ALWAYS develop gaps and can be come random
and even negative. The only purpose of AutoNumber Field
is to provide *uniqueness* to each Record in the Table and
the actual value allocated to each Record shouldn't
matter. In fact, normal users of your database shouldn't
even see the (values of) the AutoNumber Field.

If you need a sequence of consecutive numbers, search
Google or these newsgroups for "custom number sequence".

HTH
Van T. Dinh
MVP (Access)

 

[Joe Fallon]

If you need to re-number the values because they represent real things like
a Seniority Roster Number then you need to write some code to do it. (As
Union members retire, the remaining members "move up" in seniority and want
it publish every week/month - the guy who moves into the #1 slot wants it
done yesterday.)

You have to be sure that no other tables rely on the old values either!

I could probably dig up a code sample tomorrow if you need one.
Let me know.