Wrong message with cascading deletes

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
 
D

Douglas J. Steele

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?
 
L

Lars Brownies

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?
 
L

Lars Brownies

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
 
L

Lars Brownies

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.
 
T

Tom van Stiphout

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
 
T

Tony Toews [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.

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/
 
L

Lars Brownies

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
 
D

Douglas J. Steele

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!)
 
L

Lars Brownies

Thanks. That's a good idea.

Lars

Douglas J. Steele said:
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!)
 
D

David W. Fenton

I have an intense dislike for cascade deletes in Microsoft Access.

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.

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.
 
T

Tony Toews [MVP]

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.

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/
 
T

Tony Toews [MVP]

Lars Brownies said:
It seems like your main concern is the lousy message you get when you're
cascadingly deleting. I agree.

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/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top