Re: Deleting last row using cascading delete

Colin Desmond wrote:
We are using Access 2000 as our DB and using ADO to talk to it. We
have a series of tables and relationships configured using cascading
deletes.

When we have 3 rows in one of the tables (BS_ENTITY), and 3
corresponding rows in another table (BS_UNIT). None of the other
relationships apply as the other tables are empty.

We call "DELETE * FROM BS_ENTITY WHERE ID = @1" and iterate through
the three Ids in BS_ENTITY. The first two rows (id 1 and 2) are
deleted fine in both tables. In the last case, the row in BS_ENTITY
is deleted, but not the corresponding row in BS_UNIT. No exceptions
are raised through ADO.

We have tried passing in the statement as a cmdText and also
embedding it in a query, neither makes any difference.

If we simply try to delete id 3, it fails in the same way. So it
looks like we can't use cascading delete to delete the last row in a
linked table. This doesn't sound like it is a "feature" of Access, so
we presume we're doing something wrong! Any suggestions?


By "linked" do you mean that the other table is in a separate database file?
That is the usual meaning of "linked" in Access terminology. I suspect,
given your explanation, that you simply mean "child" table.
Frankly, I never used cascading deletes, always preferring to explicitly do
my own dml rather than depending on the builtin stuff that has been known to
fail. So I have no experience with this issue.

However, show the code.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.