Execute dbFailonError & Cascade Delete

[Tim Marshall]

HI all, Access 2003, Jet back end.

Rather than annoy my users in a particular app by having relationships
with enforced relational integrity refuse to delete a record with
related records, I'm using cascade delete. When I use a continuous form
and a record is deleted, Access provides a warning that there are
related records, do you want to continue (an aside - anyone know how to
trap that warning on the form on error event?). However, when I
construct SQL dynamically in VBA, say a string, strSql where strSql is
of the form "delete * from tbl_Whatever Where blah blah", and run:

DatabaseObject.Execute strSql, dbfailonerror

The warning about related records is not fired. In fact, it explicitly
says so in the help file that the warning will not be fired in this case.

So, what I've done in a couple of instances is simply make sure I know
what the relationships are with respect to the table in question and run
some DAO recordsets to see if there any related records and warn the
user when they press the button that fires the execute statement before
the button on click event gos ahead and constructs the delete statement.

This is sometimes rather arduous, especially in development where an app
may be frequently modified and more tables and more relationships added
later after launch or even throughout initial development and I'm
wondering if anyone has done anything that might be simpler.

Thanks in advance for any ideas.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[MGFoster]

Tim Marshall wrote:

HI all, Access 2003, Jet back end.

Rather than annoy my users in a particular app by having relationships
with enforced relational integrity refuse to delete a record with
related records, I'm using cascade delete. When I use a continuous form
and a record is deleted, Access provides a warning that there are
related records, do you want to continue (an aside - anyone know how to
trap that warning on the form on error event?). However, when I
construct SQL dynamically in VBA, say a string, strSql where strSql is
of the form "delete * from tbl_Whatever Where blah blah", and run:

DatabaseObject.Execute strSql, dbfailonerror

The warning about related records is not fired. In fact, it explicitly
says so in the help file that the warning will not be fired in this case.

So, what I've done in a couple of instances is simply make sure I know
what the relationships are with respect to the table in question and run
some DAO recordsets to see if there any related records and warn the
user when they press the button that fires the execute statement before
the button on click event gos ahead and constructs the delete statement.

This is sometimes rather arduous, especially in development where an app
may be frequently modified and more tables and more relationships added
later after launch or even throughout initial development and I'm
wondering if anyone has done anything that might be simpler.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When running an SQL Delete command I know before-hand which tables are
going to be affected by the Cascade Deletes so I just give a generic
MsgBox warning - just as Access does in a Form delete. Access doesn't
check to see if there are any related records, it only sees that there
is a Cascade Delete requirement on the relationship and gives its
"related records" warning.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwJKrIechKqOuFEgEQLlaQCfVTALgTtbEw5KojCxRmijiT qU/b4AoJuf
3k53/vm/V9u19B+RbTY3mp7a
=fylS
-----END PGP SIGNATURE-----

[Allen Browne]

Tim, you could do the same as MG Foster suggests: check to see if there are
any other tables that have a relation with the one you are deleting from,
and if so, whether those relations involve a cascading delete.

This kind of thing:

Function HasDeleteCascade(strTable As String) As Boolean
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = CurrentDb()
For Each rel In db.Relations
If rel.Table = strTable Then
If (rel.Attributes And dbRelationDeleteCascade) > 0 Then
Debug.Print rel.ForeignTable
HasDeleteCascade = True
Exit For
End If
End If
Next

Set rel = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MGFoster" <[e-mail]> wrote in message
news:LU*****************@newsread2.news.pas.earthl ink.net...

Tim Marshall wrote:

HI all, Access 2003, Jet back end.

Rather than annoy my users in a particular app by having relationships
with enforced relational integrity refuse to delete a record with related
records, I'm using cascade delete. When I use a continuous form and a
record is deleted, Access provides a warning that there are related
records, do you want to continue (an aside - anyone know how to trap that
warning on the form on error event?). However, when I construct SQL
dynamically in VBA, say a string, strSql where strSql is of the form
"delete * from tbl_Whatever Where blah blah", and run:

DatabaseObject.Execute strSql, dbfailonerror

The warning about related records is not fired. In fact, it explicitly
says so in the help file that the warning will not be fired in this case.

So, what I've done in a couple of instances is simply make sure I know
what the relationships are with respect to the table in question and run
some DAO recordsets to see if there any related records and warn the user
when they press the button that fires the execute statement before the
button on click event gos ahead and constructs the delete statement.

This is sometimes rather arduous, especially in development where an app
may be frequently modified and more tables and more relationships added
later after launch or even throughout initial development and I'm
wondering if anyone has done anything that might be simpler.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When running an SQL Delete command I know before-hand which tables are
going to be affected by the Cascade Deletes so I just give a generic
MsgBox warning - just as Access does in a Form delete. Access doesn't
check to see if there are any related records, it only sees that there
is a Cascade Delete requirement on the relationship and gives its
"related records" warning.

[mike]

On Tue, 16 Aug 2005 05:31:20 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

(an aside - anyone know how to
trap that warning on the form on error event?).

IIRC, that's not an error; it's a preference. Take a look at the
options that control confirmation. (Tools | Options, something like an
"edit" tab, something to do with record changes.)

If it's a user preference, you should probably save the preference when
your app opens, and restore it when your app exits.