Messing with autonumber

S

Sergio Biguzzi

I recovered an old Access database for new use. I simplified it removing
unwanted tables. I uaed get external data.
Unfortunately I forgot to set the primary key "ID" to autonumber and I left
it as "number". Now I have to enter manually a progressive number.
I tried to generate another primary key (with the proper autonumber
specification).
With both primary key the database works (but I still have to enetr manually
the proghrssive numbers in the original primary key.
If I delete the old primary key, I end up loosing my preexisting one_to_many
relationship.
Help!!
 
A

Allen Browne

1. Select this table on the Tables tab of the Database window.
Copy (Ctrl+C) and Paste (Ctrl+V).
Answer the dialog, "Structure only", and supply a new name for this table.
(I will use Table1 for the old table name, and Table2 for the new one.)

2. Open Table2 in design view.
Add an extra field, of type AutoNumber.
Save. Close.

3. Create a query into Table1.
Drag all fields into the grid.
Change it to an Append Query (Append on Query menu.)
Answer the dialog that you want to append to Table2

4. Drag the old Number field into the grid a second time.
In the Append To row under this second one, choose the AutoNumber field.

5. Run the query. (Exclamation icon on toolbar.)
At this point, you have an AutoNumber field that is populated with the
existing numbers.

After verifying everything, you can delete your old table. (You may need to
remove relationships first, and create them to the new table instead.)
 
Top