Update Values

[lioninwinter]

I have a set of user defined keys and deliberately omit to check data-integrity requirements for the relationships because the IDs change quite often. What is the best way to update the primary key of a particular key across all tables? Is there a VB routine that would do this?

 

[Gasman]

I do not think you should be updating any PK. Have you own keys if required for business reasons, but the PK (hopefully an autonumber) remains as first generated.

Eg You have an employee Ms Smith, she becomes Mrs Jones, but her PK does not change.

I reckon you are opening a can of worms otherwise?

Just my 2p.

 

[onur_can]

The primary key (automatic and unique number) in a table remains constant with its first value, you cannot change this number later. If you could make changes already, it wouldn't be a Primary Key. Instead of changing this, I can show you a way as a solution.
Create a new field in the row after PK in your table, set its Type as Long Integer. Set the name as FakePrimaryKey, then when you make any record on the form
FakePrimaryKey = PK
Set as, if you are going to update, you can do so through this area.

 

[Isaac]

Never use values that have any meaning, especially business meaning, for the primary key. Just don't do it.
If the business wants to "see" something that they'd like to call a "key"....That's fine. Create a user-facing column and do your best to populate it with whatever floats their boat.
Just don't 1) use it as the actual PK in the db design/joins, or, 2) verbally agree with them, when they say "I guarantee you that such-and-such logic will make it always unique". It might, it might not. They don't believe this, but as a developer, you and the dev community know it to be true.

To read:

https://www.access-programmers.co.uk/forums/threads/some-basic-help-please.312840/#post-1708947

 

[onur_can]

In principle, I create a PK that defines or represents that table in all of the tables I have created in all of my projects. Even if a relationship is not established in a table I created, they are a great guide to me when I use the RecordSet or domain community commands from that table from within VBA. In this way, you can read the codes more easily and you will know what you are doing. My advice for him is; I suggest you define a PK (Primary Key) field for each table that will describe and represent that table, even if there is no relationship between them.