Does Autonumber Screw Up Existing Numbers?

[Chris]

Hi List,

I have a table which has been manually updated with an
ID. There are unique values but there are some gaps
between numbers.

I wish to make this field an AutoNumber field, so the ID
is automatically added to the last number available in the
field.

If I was to do this, would it alter the numbers and/or
fill in the gaps (not preferable), or would it simply add
the next consecutive ID for the new record.

It is really important for us to retain the original IDs
in the proper records.
Chris

 

[Scott McDaniel]

No, changing the datatype to Autonumber won't cause this problem. If you do
change to Autonubmer, however, it's a one way change ... you can't change
back to the old datatype.

 

[Tim Ferguson]

As Scott says, no it won't renumber old records, and will start from the
max number and work up. Not necessarily consecutive, but you prolly already
know that.

No, changing the datatype to Autonumber won't cause this problem. If
you do change to Autonubmer, however, it's a one way change ... you
can't change back to the old datatype.

Except this answer is the wrong way round. Autonumber -> number easy;
number -> Autonumber harder!

What you need to do is to

(a) make sure you have a reliable backup or two...

(b) create an empty copy of the table -- use Paste Structure Only

(c) change the Integer PK field to Autonumber/ Increment but keep the field
name the same.

(d) append the data from the old table into the new one. The existing
values in the (new) autonumber field will be preserved.

(e) delete relationships, rename the old table, rename the new one to the
old name, and recreate relationships. Check indexes etc all okay.

Hope that helps

Tim F

 

[Steve Schapel]

Chris,

Access won't let you change a field to AutoNumber data type if there
is already data in the field. You will need to make a new blank table
with the same structure as the existing table, except with the ID
filed as an AutoNumber insead of the existing data type, and then use
an Append Query to move the data from the existing table to the new
one. This will have the desired effect.

- Steve Schapel, Microsoft Access MVP