How does Access change primary key value within an RI relationship?

[Dean Slindee]

We are using .adp projects with a SQL Server backend. Lookup tables
typically have a single field for
the key to a lookup table that populates combo box lists.

These lookup values are stored "as is" in the detail data tables, and RI'd
back to the lookup tables. Example: lookup table primary key values for a
Color table, primary Key (ColorID): Red, Brown, Blue, Green, etc.

Data table has a column named ColorID that is RI'd to lookup table, with
Cascade Update ON.

What or how does Access do underneath the covers when I change the "Red"
value in the lookup table to "Rust"?
I want to be able to do the same process in VB.NET (change primary key
value), rather than do a Insert/Delete, rebuild RI and Cascade Update
settings.

Thanks,
Dean S

 

[Tony Toews]

We are using .adp projects with a SQL Server backend. Lookup tables
typically have a single field for
the key to a lookup table that populates combo box lists.

These lookup values are stored "as is" in the detail data tables, and RI'd
back to the lookup tables. Example: lookup table primary key values for a
Color table, primary Key (ColorID): Red, Brown, Blue, Green, etc.

Data table has a column named ColorID that is RI'd to lookup table, with
Cascade Update ON.

What or how does Access do underneath the covers when I change the "Red"
value in the lookup table to "Rust"?

Access changes the value in the Colour table and changes all the
values in the foreign keys as well. I'm sure this is all bound in a
transaction so that if the update fails for any reason that all the
changes can be roll backed.

Note though that I much prefer using autonumber primary keys for every
table so that you don't have to worry about Cascade Updates.

Several other former and current MVPs disagree with me and prefer the
lookup tables as you have described. <smile>

Also note that this discussion can sometimes be referred to as
religious or fanatical. Not quote but close. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Brendan Reynolds]

Jamie, if you'll look again at the original post, this is an ADP. JET is not
involved.

In this scenario, it is SQL Server that is performing the cascade action,
and, to the best of my knowledge based on the information available, I would
expect SQL Server to go right on doing that regardless of whether the UI is
an Access ADP or a VB.NET app. I know of no reason why the original poster
should need to do anything special to achieve this in the .NET app.

 

[Brendan Reynolds]

I thought the OP was implementing their own cascade update actions in
their .NET app.

It does sound that way, yes. But if that is the case, then I think we need
to ask 'why?' Perhaps there is a valid reason. Or perhaps the original
poster was not aware that SQL Server could do it.

 

[Brendan Reynolds]

I think that's unlikely, Jamie. I don't see anything in the original post
that implies that type of relationship. But perhaps Dean (the original
poster) might be kind enough to clarify the question for us, and then we
won't have to speculate.

 

[Tony Toews]

otherwise someone like me may feel compelled to point out
that your autonumber 'primary key' will fail in its job to provide
effective protection from duplicates.

But autonumber primary keys within Jet are unique. Although Jet
creates them as Indexed - Yes (No Duplicates).

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Jamie, if you'll look again at the original post, this is an ADP. JET is not
involved.

And I missed that detail as well. Thanks for pointing that out.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

Consider this simple example:

If so, how would adding an autonumber PRIMARY KEY, without additionally
constraining the natural key as UNIQUE, help eliminate the duplication?

Ah, yes, I hadn't thoroughly read your answer so I see your point.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm