change of primary key

[elliot315]

How do I reassign a new PK for may tables.. they're already related and there about 2,000 records on the Main Table and like 6000 records on the related table.... I used a wrong PK and I want to create a new (long Integer Auto-Number) but I don't want to lose what I've done already.

 

[The_Doc_Man]

Wrong key. Like... wrong size?

Well, here's the overview. I've broken it down into very simple steps so you can be clear on what is happening when.

# 1 (never to be skipped when doing something like this): Make a good copy of your database. Backup to tape or CD or another hard drive or do an outright copy or do SOMETHING so that you have two files that are just alike. You will keep that copy for the occasional "whoops" that all of us have sometimes. It is your safety net. Treat it like gold.

#2 - In the same DB, create a new table that looks JUST LIKE the main table - with one more field that is an autonumber but not the PK.

#3 - Create a new table that looks like the related table but with one more field that is a long integer. No relationships - yet.

#4 - Now write an append query to load up records from the original main table to the new table. Copy field for field. But remember you added one more field, an autonumber. Let it do its thing, number-wise. When you have appended all of the records, the autonumber will be ripe for use. But... leave the original key as still a key.

#5 - Now build a temporary relationship between the new main table and the old related table BUT still using the old keys.

#6 - Write a JOIN query that updates the new field in the related table from the new field in the main table. You are still joining on the old key but building a secondary set of pointers from the related table to the appropriate record in the main table.

#7 - You're nearly there... Now delete that relationship between the new main table and the old related table. (Told you it was temporary!)

#8 - Now remove all keys from the main table and any key involving the old key field from the related table. (Dont' remove the fields - just make them no logner act as keys.) If you had relational integrity turned on, you must turn it off now.

#9 - Now select the NEW field (that is autonumbered) on the main table as your new prime key.

#10 - Define a relationship between the new (autonumber) PK and the new related table's new long integer field.

#11 - Last step: Spot-check that everything works.

#12 - If so: Delete the (now obsolete) former PK/FK fields from the new tables. And delete the old tables entirely.

#13 - If no: that's why you made a backup copy. Start over again.

 

[elliot315]

I'll do it right now and will tell you if i succeed.. THANK YOU

 

[elliot315]

I got stucked after the append... I have the new main table wit the new field and appended all the record from the original main table.... now I'm lost because I don't understand the Join query.. before you told me to create a copy of each table with one more field... but later you said to join the tables the new main table with the related table with the new field (is this a new related table.. or the old related table with one new field?

 

[elliot315]

Im lost on step 6

 

[Dennisk]

I thought if you had cascade updates set and you changed the PK Access would automatically update the FKs for you. I seem to remember testing this out once.

 

[The_Doc_Man]

#6 - Write a JOIN query that updates the new field in the related table from the new field in the main table. You are still joining on the old key but building a secondary set of pointers from the related table to the appropriate record in the main table.

My bad. This was written late in the evening.

Join the new main table to the old related table on the old keys, which are still in effect. Update the new numeric field of the old related table from the new (and now populated) autonumber field of the new main table.

Proceed from there.

 

[The_Doc_Man]

Dennisk, you are right - but he claims there is a problem with the key such that he needs to change its format. Cascade update won't do THAT for you. (At least I don't THINK it will.) But if I misinterpreted his dilemma, then I've sent him on somewhat of a wild goose chase.