Cascade Delete and Update Issue

[Linda RQ]

Hi,

Access 2003. Using my database on a network drive. I have a front and back
end. In my relationships on my back end I have 3 related tables by PtID. I
have checked the box enforce referential integrity and the cascade delete
and updates. When I look at my relationships from my front end, the dialog
box is greyed out but I can see that Enforce referential integrity is
checked but the other 2 are not. I have my tables linked to the front end.
I think this is causing my problem with deleting a record. If a patient was
entered in error, the user deletes that record using the record selector on
the form but the patient won't actually delete. I have to go do it from the
table. I am still a beginner at access so I may need help on more details
to my question, so let me know what else you need.

tblPatients
PtID-AutoNumber

tblPtThpy
PtThpyID-AutoNumber
PtID_fk-Number
ThpyTypeID_fk-Number


tblPtLoc
PtLocID-AutoNumber
PtID-Number


Thanks,
Linda

 

[Roger Carlson]

You have to set Cascade Deletes in your Back-End database. If you can see
that it's not checked in the Front-End, then it's not checked in the BE.

BTW, when using autonumber primary keys, Cascade Updates is useless (you
can't change the PK value anyway) so there's no sense in checking it.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Linda RQ]

Ok about the Cascade updates...I think...I'll have to concentrate on that
concept a bit because I am not completely versed on this yet.

The Cascade Deletes are set in the back end. On the front end, the Enforce
check box is checked but appears grey, on the backend it's black and I can
deselect and select. On the front end, the cascade delete is not checked
but on the back end it is checked and I can select and deselect it.

Linda

 

[Pat Hartman (MVP)]

The front end should show you what the back end has defined. The definition
of RI occurs ONLY in the source database. Try refreshing your links if you
are seeing something different in the FE than what you are seeing in the BE.
Also, are you sure you are linked to the database you think you are linked to?

 

[Roger Carlson]

Cascade Deletes: What Pat said. ;-)

Cascade Updates: Cascade updates means this: if you change the value of the
primary key in the table on the "One" side of the relationship, all the
related foreign key values in the "Many" side table will be updated to
match. This makes sure you don't get orphaned records.

But here's the thing. If your primary keys are autonumber fields, you CAN'T
change the value, so there's no sense in setting the Cascade Updates
property. You're never going to use it. The only time CU is useful is if
your primary key field is NOT an autonumber.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L