Is there a way to change a primary key value (1 Viewer)

I was wondering if the value of a primary key can be changed. The information being keyed in the table is from another (non Access) database. The primary key generated uses the first 4 letters of a persons surname and then adds an autonumber to the end.

To avoid confusion, I have been using this same field as my primary key. Now I have an error. Employee Barrows EmpID was incorrectly keyed in as BARR04 when it should be BARR07. There is data in tables related to the employee table and these have regular autonumber ID primary keys.

Is there a way to correct this error without loosing data?

I don't understand (a couple things, but I'll focus on your issue first)-- If you know what records need to be corrected, then why can't you just correct them? Update the BARR04 values to BARR07.

As for the bigger I-don-t-understand: Why aren't you using the autonumber itself as the foreign key to those other tables? This is the exact reason we tell people to use them.

I admit that I made a non-rookie rookie error

The problem is trying to correct related records - the changes are not being accepted as they are related to the EmpID primary key.

I can't delete and re-enter as it will change the numbering in the autonumber field of these tables which will ultimately affect the report formatting.

I know...I messed up up royally.

I've thought of a round-about way of fixing this by adding another field but was just wondering if there was a way to correct a primary key error...but I guess this can't be done.

Thanks anyway

I can't delete and re-enter as it will change the numbering in the autonumber field of these tables which will ultimately affect the report formatting.

Click to expand...

An autonumber is UNIQUE and only UNIQUE
--not sequential /incremental
--not necessarily positive and
--not reused

The problem is trying to correct related records - the changes are not being accepted as they are related to the EmpID primary key.

Click to expand...

This does not make sense. Related records means they are in a different table and you are using the EmpID as a foreign key in this table. Which means the field holding the foreign key isn't an autonumber field, most likely a Short Text field. Which means you should be able to change it to whatever you want.

This sounds like the perfect time to start using autonumber primary keys correctly.

I believe that if you have relational integrity enabled with Cascade Update, changing the primary key will change the foreign keys of any tables for which there was a formally declared relationship. Unless that feature doesn't work like it says it does. And if the keys in the other tables don't have a formal relationship, then a simple update query should do the job.

The PK of the employee table is this alpha numeric field
This field is used as the FK in related tables.

a) if you have relational integrity established, then changing the PK should propagate through.
b) if not, then you need to identify the FK usage, and change the values by use of update queries
c) another more complex way is to add an autonumber field to the employee table, and then change all related tables to use the autonumber key as the FK, but this will involve more work.

gemma-the-husky said:

The PK of the employee table is this alpha numeric field
This field is used as the FK in related tables.

a) if you have relational integrity established, then changing the PK should propagate through.

Click to expand...

Only if you enable the "Cascade update related fields" option. Tophan needs to enable that option on the relationship. Double-click on the relationship line in the Relationships window to see the option.

Once that is done the update should be straightforward. As a rule, it usually makes sense to enable the cascade update option on your referential integrity constraints. The exception is when the key is an autonumber which cannot be updated and so the cascade option doesn't achieve anything.

Thank you all so much for your help on this problem...I really do appreciate it and know I made a critical error in setting this database up. The problem is that this database was needed in a hurry and is still being built as it is in use. In one week it already has close to 400 records entered.

I found a round-a-bout but surprisingly quick way of fixing the primary key error by creating a "dummy" employee..i.e....Me . By changing the SubID on the related table to my code, the table with the incorrect SubID as the primary key showed that there were no related records for that person. I was then able to correct it and change back all the records with my ID in the related table to the correct person.

What I would like to do is build a proper better thought out database and then import the information from this current one into the new and better one. I just think at the rate we have been entering data that time will be the problem.

Thank you all again and I know I messed up but I have learned form my mistakes and will make sure not to repeat in the future. I will try all your suggestions, especially the cascade update related fields in the relationships which is not checked in the relationships.