Need to Keep Original IDs for new AutoNumber Field

[Chris]

Greetings, here is my problem:

I have a table with a Primary_ID field, whose numbers I
wish to retain for historical purposes.

I wish to convert this field into an AutoNumber field, so
any new records will build on these numbers consecutively.

However, Access will not allow any field with existing
data to be converted into an AutoNumber field.

Does anyone have a work-around for this?

Many thanks in advance!
Chris

 

[Dirk Goldgar]

Greetings, here is my problem:

I have a table with a Primary_ID field, whose numbers I
wish to retain for historical purposes.

I wish to convert this field into an AutoNumber field, so
any new records will build on these numbers consecutively.

However, Access will not allow any field with existing
data to be converted into an AutoNumber field.

Does anyone have a work-around for this?

One solution that I think would work is:

1. Copy the structure (not the data) of the original table to a new
table. You can do this by copying/pasting the table from the database
window, and choosing to copy only the structure.

2. In the new table, change the Primary_ID field to autonumber.

3. Create and run an append query to copy all records, all fields
(including Primary_ID) from the original table intto the new table.

4. If there are tables with enforced relationships to the original
table, note down what those relationships are, then drop them.

5. Rename the old table to a "junk name", and rename the new table to
the original table's name.

6. Recreate the relationships you dropped in step 4.

 

[Guest]

Ok Dirk, it seems kinda 'out there', but maybe it'll
work. I'll try it, thanks!

Chris