Attempting to delete a record without Cascade Delete Records checked

[GoodWrench]

Hi all,

Relatively new VBA and access programmer here.

I have a question regarding deleting records and referential integrity. I'm trying to delete a record from my table "TblLandowners" which has a one-to-many relationship with "TblTracts" via the field "LandOwnerID" (Landowners may own many tracts of land). In the relationship editor I have checked "Enforce Referential Integrity" checked and "Cascade Update Related Records" checked, but NOT "Cascade Update Delete Records." The reason for this in a nutshell, is that there is almost never a reason to delete a Tract in our database, only to change the Landowner of that Tract. I don't want a user to delete a Landowner record, accidentally "cascade deleting" any associated Tract records. Instead, if a user would like to delete a Landowner that is in current ownership of a Tract, they must first delete the associated tract, or change the landowner of that tract.

My issue is that I have seen (or misinterpreted) conflicting information about what happens when you try to delete a record in the situation described above.

When I attempt to delete a Landowner record where a Tract has a foreign key pointing to that Landowner record, using:

DoCmd.RunCommand acCmdDeleteRecord

nothing seems to happen, which is what I want. Although I want to make sure it isn't something else happening somewhere in my code or design, preventing the delete from happening.

I have read elsewhere (or again misinterpreted), that when deleting a record in this relationship, that the record on the "One" side of the relationship (Landowners), will in fact be deleted, but the record on the "Many" side of the relationship (Tracts) will just lose the foreign key, and be an "Orphan" record. This is not what I want.

If my experience is correct, I'll basically just perform a check when a user attempts to delete a landowner record informing them that "This landowner is currently the owner of the X, Y, and Z tracts. Please delete this tracts or change their landowners before deleting this landowner."

Thank you for any help. I've been "lurking" these forums for the past couple of weeks and all of the information I've found in the exchanges here has been immensely helpful!

 

[sneuberg]

I have read elsewhere (or again misinterpreted), that when deleting a record in this relationship, that the record on the "One" side of the relationship (Landowners), will in fact be deleted, but the record on the "Many" side of the relationship (Tracts) will just lose the foreign key, and be an "Orphan" record. This is not what I want.

That's what happens if relational integrity is not enforced. I think you must have misinterpreted it.

I don't know why DoCmd.RunCommand acCmdDeleteRecord isn't raising an error. I know that

CurrentDb.Execute "DELETED * FROM TblLandowners WHERE [LandOwnerID] = " & Me.LandOwnerID

doesn't unless you add dbFailOnError. In any case we've found it better to detect the problems with DCount than to try to trap the errors. So in your forms when you do a delete I suggest adding something like before the code that deletes the record.

If DCount("*", "[TblTracts]", "[LandOwnerID] = " & Me.LandOwnerID) > 0 Then
    MsgBox "Record can't be delete because is has tracts"
    Exit Sub
End If

You have to do this for each child table which is a lot of work but the advantage is that the messages will make sense. If you just trap the errors you can't say which child table has the related records.

 

[GoodWrench]

Many thanks for the clarification. That DCount function works like a charm. I'll do as you say and use this for each of the child Tables.