Logging Cascading Deletes/Updates

[ExcelMan]

I have an application where I log changes made by users through the
forms in the app. During the Form_AfterInsert, Form_BeforeUpdate and
Form_Delete events I capture the necessary information and store it in
a table.

The problem I have is that some forms (on which I include a delete
button) include records which have child records in other tables. When
the user deletes a record, the child records are also deleted. But
there is no Form_Delete event in which I can capture whats happening to
the child records.

Outside of custom coding for each form that has this characteristic,
does anyone know of a technique to detect when a cascading action is
taking place. If I can detect it, I can add code to handle it when it
happens.

Thanks.

 

[Allen Browne]

You can read the Attributes of each Relation the table is involved in, to
see if any cascades are occurring. The code below shows how to loop through
the loop through the Relations of the Database, and identify the table,
foreign table, and attributes.

If you do find a cascading relation, then it is possible that the related
table where the cascade is occurring is itself involved in further relations
with cascades, and so on. This means you would need to write recursive code
to track down all the cascades to an unknown depth. IIRC, JET does not
prevent circular cascades, so your code would need to pass its own trail as
an array argument to prevent infinite recursion.

On a different matter, you are aware that the user can select multiple
records in a continuous form or datasheet, and perform a delete on the lot?
You are handling the multi-record deletion, along with the possibility that
the deletion is cancelled?

Sample of looping through the relations to see the attributes:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function