How to set rows as "Delete Restrict?"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Anybody know how to set a table to delete restrict so that if a child table
exists with one of its unique keys in use, it cannot be deleted until the
child record is deleted?
 
Andy

A child table row can't be deleted until the child table row is deleted?
Clarify please...

Jeff Boyce
<Access MVP>
 
Sorry, been up all night on this thing - my brain functions are shutting down
one by one. Let me give you the exact specs for this:

"Establish and enforce referential integrity between related tables, such
that all relationships should be established as "delete restrict,"
prohibiting the deletion of a parent row so long as related children rows
still exist."

Thanks,
--Andy
 
Andy,

I have used this with success:

Private Sub cmdDelete_Click()
Dim X As Variant
X = DCount("*", "ChildTableName", "[UniqueKey] = '" & Me.UniqueKey& "'")
If X > 0 Then
MsgBox("You cannot delete this record because ...")
Exit Sub
End If

HTH,
Debbie

| Anybody know how to set a table to delete restrict so that if a child table
| exists with one of its unique keys in use, it cannot be deleted until the
| child record is deleted?
 
Hum, that is a interesting referential integrity question, and it happens to
be one feature that JET (the default database engine use for ms-access) does
not have.

ms-access + JET supports cascade deletes, and cascade updates on the
tables..but does not have a delete restrict!

The end result here is that you will have to code this...

The default data engine with ms-access does not support this feature. In
fact, I am not even sure if Microsoft sql server, or even Oracle supports
this feature.

However, if you are using a server based engine with ms-access, then you
could likely build a trigger (do note that ms-access does ship with 2 data
engines on the office cd for use with ms-access, and one engine does support
triggers).

Anyway, if you are using the JET engine, then *most* interface to the
application is via forms. The solution here is to thus simply write your own
delete code for the forms..and I usually do this anyway.

So, the code behind the delete button might look like:


' Deletes a main record, called from ClientEdit menu

Dim MyAForm As Form
Dim lngMainId As Long
Dim rstPastTours As Recordset
Dim bolDelete As Boolean


Set MyAForm = Screen.ActiveForm
lngMainId = MyAForm!ID

' check if it is legal to delete this record...

Set rstPastTours = CurrentDb.OpenRecordset("select main_id from tblBgroup
where main_id = " & lngMainId)

If rstPastTours.RecordCount > 0 Then
bolDelete = False

MsgBox "This customer cannot be deleted due to past trips, or" &
vbCrLf & _
"having active invoice information. You must remove all history
information" & vbCrLf & _
"such as past trips and invoices before you can delete.",
vbInformation + vbOKOnly, "Sorry, name cannot be deleted"

Else
Beep
If MsgBox("Do you want to Remove this Name?", vbYesNo + vbCritical +
vbDefaultButton2, "Delete Rides Customer") = vbYes Then
bolDelete = True
End If

End If

' wrap up
rstPastTours.Close
Set rstPastTours = Nothing

If bolDelete = True Then
CurrentDb.Execute "delete * from tblmainclient where id = " &
lngMainId
DoCmd.Close
End If

Note that in the above our delete command would in fact automatically delete
the child records if we did go ahead and allow the delete, this is because
JET does in fact support automatic deleting of the child records for
you...but it does not have the ability to prevent deletion of a parent
record...so, you have to code this your self...
 
Back
Top