Wrong message with cascading deletes

  • Thread starter Thread starter Lars Brownies
  • Start date Start date
L

Lars Brownies

A2003: Table Person linked 1 X M to table Activity. The relationship has its
Referential Integrity checkbox and the Cascade Delete checkbox both checked.

When I delete a person all related Activity records are deleted which is to
be expected. According to
http://www.informit.com/articles/article.aspx?p=26115&seqNum=5 I should get
the following message in this case:

"Relationships that specify cascading deletes are about to cause 1 record(s)
in this table and in related tables to be deleted. Are you sure you want to
delete these records?"

In stead I get:
"You are about to delete 1 record(s). If you click Yes. you won't be able to
Undo this Delete operation. Are you sure you want to delete this records?"

Can someone explain what's going on?

Thanks,

Lars
 
Are you certain your relationships are set up properly?

Is there a 1 at the Person end of the relationship and an infinity sign at
the Activity end in the Relationships diagram?
 
Yes, it's set up like that.

Lars

Douglas J. Steele said:
Are you certain your relationships are set up properly?

Is there a 1 at the Person end of the relationship and an infinity sign at
the Activity end in the Relationships diagram?
Click to expand...
 
I imported only the respective tables into a new db, made the exact same
link, and that works fine.

The Person table is linked to about 15 other tables, so I'm thinking it has
something to with them and their relationships(?)

Lars
 
I did some further testing and this seems like a bug. In a new mdb I created
the following tables:

1. tblPerson: PersonID* LastName
2. tblPersonActivity: PersonID* ActivityID*
3. tblPersonAdress: PersonID* AddressID*

I linked 1 to 2 (1xM) and 1 to 3 (1xM), both with RI and cascading deletes
checked.
Table 1 has one record and there's one related record in table 2. Mind you:
Table 3 is empty.

If I delete the tblPerson's record I don't get the cascading deletes
message. If I remove the link from table 1 to 3 and try again, then I do get
the cascading deletes message.

Now if I repeat the above and first adding a record to table 3, I do get the
right message.

So if one of the relation tables is empty, you don't get the right message.

This doesn't seem like WAD.

Lars

PS: I skipped the Activity and Address tables and they don't make any
difference to the above.
 
On Sun, 23 May 2010 20:14:18 +0200, "Lars Brownies"

I am able to confirm this behavior with A2010 accdb. This is indeed at
least weird and possibly a bug. Report it.

-Tom.
Microsoft Access MVP
 
Lars Brownies said:
A2003: Table Person linked 1 X M to table Activity. The relationship has its
Referential Integrity checkbox and the Cascade Delete checkbox both checked.
Click to expand...

I have an intense dislike for cascade deletes in Microsoft Access.
And I don't like cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
It seems like your main concern is the lousy message you get when you're
cascadingly deleting. I agree. For some relationships I wouldn't want
cascading deletes because it's too risky, but for other relationships I
think the built in cascading deletion can come in handy.

I'm thinking of replacing the default message by something like:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

Response = acDataErrContinue

If MsgBox("Warning!" & vbCrLf & vbCrLf & _
"Deleting this record will also delete " & _
"the related records in the Activity table." & vbCrLf & vbCrLf & _
"Are you sure you want to continue?", _
vbOKCancel + vbExclamation + vbDefaultButton2) = vbCancel Then
Cancel = True
End If

End Sub

Lars
 
FWIW, I usually go one step further, Lars, and calculate the number of
records that will be deleted (using DCount).

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)
 
I have an intense dislike for cascade deletes in Microsoft Access.
Click to expand...

I don't see it as any different than a trigger. I use it sparingly,
though, as in most cases, I want to prevent the deletion of any
parent record with children.
And I don't like cascade updates.
Click to expand...

They are appropriate where they are appropriate. This would exclude
all Autonumber foreign keys, of course, though Access tends to
default to that, anyway.

I don't see how "intense dislike" is an appropriate response to
these features of a database engine. You use them where they are
appropriate and don't use them where they are not. What's to get
upset about?

I see no reason to dogmatically avoid either or both in all cases.
 
David W. Fenton said:
I don't see it as any different than a trigger. I use it sparingly,
though, as in most cases, I want to prevent the deletion of any
parent record with children.


They are appropriate where they are appropriate. This would exclude
all Autonumber foreign keys, of course, though Access tends to
default to that, anyway.

I don't see how "intense dislike" is an appropriate response to
these features of a database engine. You use them where they are
appropriate and don't use them where they are not. What's to get
upset about?

I see no reason to dogmatically avoid either or both in all cases.
Click to expand...

Then we agree to disagree. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
Lars Brownies said:
It seems like your main concern is the lousy message you get when you're
cascadingly deleting. I agree.
Click to expand...

Yes, that's one reason as the user won't understand the ramifications
or even notice the extra words in the message.

However I seldom allow the users to delete records anyhow. I usually
make the parent records inactive or such. I like keeping history
around.

Now when entering transaction data such as invoice details for an
invoice which will be canceled then sure. But that's fairly seldom
and I don't mind spending the extra minute or two doing the coding.

This also, ahem, saves on developer error too. Not that I've ever had
that happen to me. <smile> <whistling and looking around>

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
Back
Top