Change the Primary Key Value of a Record

[tmm786]

I am wanting to change the Primary Key value for data entries after it
has already been set. I have one main table (tblManpower) that has
every employee in the Facility, with their clock number as the Primary
Key. Linked to tblManpower are two other tables (tblDutyHistory and
tblPositionHistory). tblManpower will only have one entry per clock
number while the other two tables will have multiple entries per clock
number. So if the employee ever changes clock numbers, I'd like to be
able to change their clock number entries for all the tables. Is
there any way to do that? I've tried looking it up everywhere I can
think of and haven't had any luck.

Thanks

 

[Jeff Boyce]

If you open the Relationships window and join the tables on their shared
fields, you can set Cascading Updates to true (i.e., check it).

Another approach would be to use an arbitrary primary key and another field
for [clocknumber]. That way, you wouldn't have to "ripple" changes in the
primary key to the "child" tables' foreign key fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

If you have relationships set up between the tables and you have maintained
data integrity to this point, you could set CASCADE UPDATE on in the
relaionship window. In theory (and almost always in practice) that will
automatically update the foreign key value when the primary key is changed.
The only consideration I have ever run into was a record contention when
Cascade update was trying to do its work and someone else was modifying
records that were involved in the update - that was long ago and the problem
may no longer exist.

Or you could use an artificial primary key (such as an autonumber) and join
your tables on that and only store the ClockNumber in tblManpower. It may
be too late for that, but something to consider in the future.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tmm786]

Thanks, a lot. This really helps. The only other way I had thought
of was really complicated, but this makes it really easy. I didn't
know what the menu option was.


I appreciate the help.