adding an auto number pk to an existing table

[MikeZCG]

I have a table with 14,000 recs. There is no primary key
on it. I was thinking of :
1. copying the table,
2. deleting all records,
3. adding an auto number primary key in design view
4. using an append query to add records,
5. add the relationships and delete the existing table,
6. finally renaming the copied table to the original.

Would this be a good work around? ,, else can i have as
many suggestions as possible


Tnks

M~

 

[John Vinson]

I have a table with 14,000 recs. There is no primary key
on it. I was thinking of :
1. copying the table,
2. deleting all records,
3. adding an auto number primary key in design view
4. using an append query to add records,
5. add the relationships and delete the existing table,
6. finally renaming the copied table to the original.

Would this be a good work around? ,, else can i have as
many suggestions as possible

There's a simpler approach. Open the Tables window in design view, and
select your table. Select Edit... Copy; then select Edit... Paste, and
choose the "Structure Only" option; use a new name.

Open this table in design view and add an Autonumber field, defining
it as the PK.

THEN run your append query, define relationships, and rename.

How are your relationships defined now, if the table has no PK? Will
you need to add a Long Integer field to your related tables as a
foreign key to link to the new autonumber PK?