changing PK from XX000 to autonumber

[jjatcal]

Hi, I had originally created a database with PK of XX000 where X is letter and 0 is numeric. The XX was the first two letters of the person's last name.

This is how one book on DB design I read suggested.

I have since come to understand from folks on this site that this is a difficult way of doing it. I would like to change the PK to autonumber.

I have a handful of other tables already linked to this ID. How can I do this smoothly?

I know I must delete all relationships and then recreate them after I change the PK.

Thoughts?

Thanks!

 

[JeremyNYC]

Here's what I would do, for each "main" table and those related to it.

BACK UP YOUR DATABASE.

Back up again.

Test your back ups.

No, I mean it. Test both of them.

OK, now we can get to work...

For each "main" table and the tables related to it, here's what I would do, assuming the original PK field was called PK, and the fields in all the others were called the same thing...
1) Rename mainTable.PK to mainTable.PKold.
2) Create a new autonumber field called mainTable.PK.
3) In each of the related tables, do the same thing, except that the field you create and name PK will be a long integer field (whatever is the default field type), not an autonumber.
4) Create and run update queries that relate the tables based on the existing relationships and update relatedTable.PK to mainTable.PK.
5) Delete all fields called PKold. (Hopefully this deletes the relationships. If not, you'll have to do that manually.)
6) Re-relate the tables based on the new PK fields.

The reason to go through all the renaming is that then your queries and code that refer to that field will still work.

Hope that helps.

Jeremy

PS: Did I tell you to back up your database before you do this? It might be a good idea.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.