Autoincrement numbering

[iainking]

I'm converting my primary key to an autoincrement field. The old key
(ID) was sequential, but manually entered. To convert I create a new
field (newID), and make it of type autonumber. I switch out of design
view, and the newID field gets populated. I then check that it matches
up with the old key, then delete it and rename the newID to ID, and
re-establish any relationships. This has worked for the first three
tables, but when I try it with the last table, the autonumbers don't
match up with the old values. It seems to be stepping through the
records in the wrong order (i.e. not ID order, but in some other
order). Is there any way to get the autonumbers to match up with the
old ID's?

Iain

 

[Lynn Trapp]

Probably the best way is to create an entirely new table with the same
structure as the old one, except that the ID field is an Autonumber field.
Then run an append query to get the data from the old table into the new.

INSERT INTO NewTable ( NewId, SomeField,SomeOtherField )
SELECT OldTable.Id, OldTable.SomeField, OldTable.SomeOtherField
FROM OldTable;

 

[Joseph Meehan]

I'm converting my primary key to an autoincrement field. The old key
(ID) was sequential, but manually entered. To convert I create a new
field (newID), and make it of type autonumber. I switch out of design
view, and the newID field gets populated. I then check that it
matches up with the old key, then delete it and rename the newID to
ID, and re-establish any relationships. This has worked for the
first three tables, but when I try it with the last table, the
autonumbers don't match up with the old values. It seems to be
stepping through the records in the wrong order (i.e. not ID order,
but in some other order). Is there any way to get the autonumbers to
match up with the old ID's?

Iain

Lynn's advice will answer your direct question. However please consider
that autonumbers are not designed to assure nice incremental numbers. They
are designed to provide unique numbers. As a result you may find that as
you add new records the new numbers are not incremental and may jump around.
If this is a problem for you or other users of the data, then you need to
build your own auto-incrementing numbering system. If you just using the
number to link tables or some other use that few people see and those that
do will not be confused by the odd numbering, then you should be ok.

 

[iainking]

Thanks Lynn, I'll do that.

Joseph Meehan:

However please consider
that autonumbers are not designed to assure nice incremental
numbers.

It won't generate new numbers by simply adding one to the previous one?
It has type 'Increment'....

Iain

 

[Rick Brandt]

Thanks Lynn, I'll do that.

Joseph Meehan:

It won't generate new numbers by simply adding one to the previous one?
It has type 'Increment'....

Yes, but if you start a record and then cancel that number is "consumed" and you
will have a gap. If you were to run an append query and answer "No" to the
prompt "You are about to insert 5000 rows..." you will still consume 5000
AutoNumbers even though you cancelled.

There are likely others, but the point is if you need an unbroken sequence of
numbers, AutoNumber is not the way to get in most cases.

 

[iainking]

Ah, I see. In that case I'll probably leave it the way it is, and use
VB to generate the indexes.
Thanks a lot.

Iain

 

[Joseph Meehan]

Thanks Lynn, I'll do that.

Joseph Meehan:

It won't generate new numbers by simply adding one to the previous
one? It has type 'Increment'....

Most of the time yes, but not always. You can't count on it.