---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :
Hi David
As I said, there are occasions where I cascade deletes for relationships other than pure junction tables, but they are rare. It makes sense for something like report header/details, because it makes absolutely no sense to keep the details if the header is not needed. However, it should be an exception rather than a rule, because in most cases (customers/orders, products/purchases, students/exam results, etc) the child record contains valuable information which must be retained, but which becomes partially meaningless is the parent record is deleted.
I suppose the important question is "Will I lose important data if the child record is deleted?"
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 28 March 2017 16:05
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Error 3396
Graham, thank you for that explanation as it explains why I am now getting Error 3200 where I was getting 3396. The reason is because I had gone back to my tables and noticed the inconsistency just as you described, and changed those tables such that Cascade Delete was not allowed on any of them. I did not realize that is why my error number changed, but thanks to your explanation I now understand why.
I will have to review all of my tables against your advice, but I think earlier today I pretty much did just what you suggested. I know I made all of my many to many tables cascade delete. I made most other relationships to not allow cascade delete.
However, I know I left my transaction type tables cascade delete. These are tables where I have something like a report header table and a report details table. I made these cascade delete. In your work would you not do that?
---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :
Hi David
I believe that error 3396 occurs only in the following unusual situation:
TableA is related to TableB with cascade deletes
TableB is related to TableC WITHOUT cascade deletes
You attempt to delete a record from TableA, where related child AND grandchild records exist in TableB and TableC respectively.
Normally, if it is appropriate to have cascading deletes from parent to child, then it would also be appropriate between child and grandchild.
Personally, I commonly use cascading deletes on junction tables which usually contain no data other than the representation of a many-to-many relationship. Otherwise I would use them very rarely, and never (that I can think of) in the three-table scenario described above.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 28 March 2017 12:20
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3396
Yes, I am only seeing only the system message. I am getting one of two errors - either 3200 or 3396. I have attempted to trap both of them in the same manner you did with the other errors, but to no avail. I added both to the globals module and added both to the CASE statement in the FORM ERROR and in the SAVE function.
Neither the 3200 nor the 3396 errors are being trapped. I set error 3396 to errCascadeDelete and error 3200 to errCascadeDelete2. I added them to the CASE statement as with your other cases.
Case errCascadeDelete 'attempting to delete a Parent record with children where cascade delete is not allowed
MsgBox "You are attempting to delete a record which has related 'child' records. " & _
"You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
Response = acDataErrContinue
Case errCascadeDelete2 'attempting to delete a Parent record with children where cascade delete is not allowed
MsgBox "You are attempting to delete a record which has related 'child' records. " & _
"You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
Response = acDataErrContinue
The error trapping code works fine for the case of duplicate record errors, so I think the sub and function are in general correct.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
Form_Error or error trapping code in your Save code should see that error. Are you only seeing the system message? Maybe it's being triggered with a different error code.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Mar 27, 2017, at 6:35 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thanks John, I will take the time to understand your code and use it as I am sure you have good reason to do it this way. I also never use cascade update because all my primary keys are autonumber. However, I did think I should have some of my tables set for cascade delete. You chose to never to allow that? I can see where that is the safest choice, but sometimes would be very inconvenient.
I did try to add the error 3396 error handling to your form error code and to your error function code. My custom message and error handling did not appear though. Is this because this is a class of error which would not be caught by either of these two methods?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
I generally disallow Cascade Update and Cascade Delete. I avoid that error in the Contacts app because a) all Primary Keys are AutoNumber, so cascade update will never happen, and b) my code checks before allowing a delete and issues a custom message if you would get a cascade error. For example, here's the code in frmCompanies:
Private Sub Form_Delete(Cancel As Integer)
Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant
' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateCompany")
' Set the company parameter
qd!CompanyNo = Me.CompanyID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot delete this Company because you have related rows in " & _
varRelate & ". Delete these records first, and then delete the Company.", _
vbOKOnly + vbCritical, gstrAppTitle
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' Cancel the delete
Cancel = True
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to delete Company " & Me.CompanyName & "?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
Cancel = True
End If
End Sub
qryCheckRelateCompany is:
PARAMETERS [CompanyNo] Long;
SELECT DISTINCT "Contacts" As TableName, CompanyID
FROM tblCompanyContacts
WHERE tblCompanyContacts.CompanyID = [CompanyNo]
UNION ALL SELECT DISTINCT "Invoices" As TableName, CompanyID
FROM tblInvoices
WHERE tblInvoices.CompanyID = [CompanyNo];
tblCompanyContacts and tblInvoices are the two tables that will not allow you to delete a company if related rows exist.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Mar 27, 2017, at 3:05 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I see that your error handling in CONTACTS does not include handling error 3396, the cascade delete error.
How do you handle the cascade delete situation? I have some places where I set my table relations to allow cascade delete and some places where my table relations do not include cascade delete. Do you make cascade delete extra hard to do? Two warning messages or something of that nature?
Posted by: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar