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;

 

[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