Share via

Changing unique IDs across multiple tables in Access

Anonymous
2018-08-15T17:36:58+00:00

I have a question about changing unique identifiers. 

The main database that I extract data from and enter into an Access database has had some changes over the past few years.  I have a handful of records that now have a different HMIS ID than they do in my Access database.  I'd like to update them in Access so they are uniform across both but am concerned about how to do it, or if it is possible to make a successful change in Access because they are unique identifiers.  These unique IDs are tied to multiple tables and queries.  Any advice is welcome.

Thanks

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

ScottGem 68,820 Reputation points Volunteer Moderator
2018-08-15T18:05:17+00:00

You need to try this first on a copy. Use the Relationships window and make sure all your relationships have Referential Integrity and Cascade Updates set.

Then, assuming your keys are not Autonumbers, you can then update the primary key and the child records should update.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2018-08-15T17:52:43+00:00

    I would start by making a backup. Then create a table of the current and new values. Set both of the fields to unique using indexes. You can then add new fields to the tables and perform some update queries. Then rename the old then new fields. Update the relationships to the new fields to ensure the integrity.

    Was this answer helpful?

    0 comments