Maintaining a Primary which may change

[Guest]

Hello,

The two main purposes of my Access 2003 project are to:
1. Track people who are assigned to a project, and their progress through
the project hiring process.

2. Track these employee's access to a number of project-related
applications.

For purpose #1:
When an employee is assigned to a project, the following happens:
1. Create a computer account.
2. Create email account
3. Grant access to a number of different applications.....

1-3 are related to tracking progress through the hiring process. First, a
computer account (username) is requested & created. This may take 2-3 days.
However, the employee's master record must be entered into the database
before the computer account is created. Therefore, a dummy/temporary
username is created. This is the PK for the master record. It's also a FK
for other tables where there are both 1:1 and 1:many relationships.

Does Access 2003 support a downward cascading update feature so that when
the permanent username/PK becomes known, it can be updated on the
master/parent record and child records/tables?

TIA,
Rich

 

[Andrew]

Hello,

The two main purposes of my Access 2003 project are to:
1. Track people who are assigned to a project, and their progress through
the project hiring process.

2. Track these employee's access to a number of project-related
applications.

For purpose #1:
When an employee is assigned to a project, the following happens:
1. Create a computer account.
2. Create email account
3. Grant access to a number of different applications.....

1-3 are related to tracking progress through the hiring process. First, a
computer account (username) is requested & created. This may take 2-3 days.
However, the employee's master record must be entered into the database
before the computer account is created. Therefore, a dummy/temporary
username is created. This is the PK for the master record. It's also a FK
for other tables where there are both 1:1 and 1:many relationships.

Does Access 2003 support a downward cascading update feature so that when
the permanent username/PK becomes known, it can be updated on the
master/parent record and child records/tables?

TIA,
Rich

Certainly does....

When you create the relationship between the two tables (Tools -->
Relationships), access will prompt with a dialogue box to confirm the
settings for that relationship. As long as the various criteria (same
data type / field size etc) are met, then you will be able to click
the "Enforce referential integrity" button, which is what makes this a
"proper" relationship. At tha point, 2 more check boxes become
available - Cascade update related fields and Cascade delete related
records.

The first of these is what you need. The second can be dangerous as it
means that a "simple" deletion can result in multiple other records
being deleted too - better to implement some form of code to check
that this really is what the user wants, and quite probably mark the
records as "inactive" rather than actually deleting them.

A couple of other points:

Firstly, when creating a 1:Many relationship, access is able to
determine which table is the parent, and thus the direction of
cascades. When the relationship is 1:1, this is determined by the
direction in which you drag to create the relationship. In other
words, if you drag FROM, say, tblStaff TO tblComputerAccounts then you
are determining that tblStaff is the parent and updates will cascade
from here to tblComputerAccounts. This can be adjusted in the Edit
Relationships dialogue box, but it's easier not to have to change it
here.

Secondly, it's often considered good practise not to have PKeys which
change - they should not be meaningful data, but simply a unique
value. This would mean that the username would not be the PKey,
(although you might certainly index it to guarantee uniqueness), but
that you'd rather create some ID field to use as the key. THat way,
there's no need to cascade updates because a "real" username has now
been created, or because, say, John Smith has changed his name to E.
Presley and needs a new username....

Hope this helps

Andrew

 

[Guest]

Thanks very much for the reply Andrew. I think I almost knew the answer, but
did not realize it. My understanding of the "cascade update related fields"
was a little off.

To confirm my understanding, selecting both checkboxes will enforce
integrity AND automatically update all the foreign keys so that they are the
same as the primary key in the parent table of each relationship?

Thanks,
Rich

 

[Andrew]

Thanks very much for the reply Andrew. I think I almost knew the answer, but
did not realize it. My understanding of the "cascade update related fields"
was a little off.

To confirm my understanding, selecting both checkboxes will enforce
integrity AND automatically update all the foreign keys so that they are the
same as the primary key in the parent table of each relationship?

Thanks,
Rich













- Show quoted text -

Well, yes, as long as by "selecting both checkboxes" you mean both
"Enforce referential integrity" and "Cascade update related fields".
There is, of course, also the "Cascade delete related records"
checkbox....

Andrew

 

[Guest]

Understood!!!

Thanks very much!!!

Well, yes, as long as by "selecting both checkboxes" you mean both
"Enforce referential integrity" and "Cascade update related fields".
There is, of course, also the "Cascade delete related records"
checkbox....

Andrew

 

[John W. Vinson]

Does Access 2003 support a downward cascading update feature so that when
the permanent username/PK becomes known, it can be updated on the
master/parent record and child records/tables?

Yes. Define an enforced relationship between the tables, and check the Cascade
Updates property of the relationship.

John W. Vinson [MVP]