Editing the Primary Key 1

[beverlee]

I need to edit an employee ID number that was entered into the database incorrectly. Unfortunately, the EmployeeID is the primary key for the table and it is linking to foreign keys in other tables. Is there a way that Access will let me change this without deleting relationships, etc.? I have referential integrity enforced so it is currently not allowing me to make any changes. Thanks for the help in advance!

 

[RoyVidar]

If you don't need to do this programaticly, then enter the relationship window, right click the relatinship, select Edit and check the "Cascade Update Related Fields" option.

This will enable you to change the PK values, and the related foreign keys would cascade update. Remove this again after the operation, if you don't want the users to have this possibility.

Roy-Vidar

 

[Golom]

Depends on how referential integrity is specified. You have apparently set it to "prevent". If you use "cascade Update related fields" then the change in the master should automatically change corresponding values in child records. Cascaded RI is however a bit scary.

An alternative may be:

[li]Create a copy of the record with the correct ID[/li]
[li]Run an update query on the child tables that changes old ID to the new ID.[/li]
[li]Delete the old incorrect ID from the master.[/li]

 

[beverlee]

Thanks for the help. I thought I had the cascade update checked but I didn't. That did the trick!