Delete records from multiple tables in Access 2007

Question

Using the following data, I am attempting to delete matching records from both tables.

Fruits

ID Value
1 Apple
2 Pear
3 Banana
4 Grape

Animals

ID Value
1 Bear
2 Monkey
3 Apple
4 Pig

There is no defined relationship between these two tables.

Since "Apple" appears in both tables, I would like to remove this record from each of them.

I've tried the following query to accomplish this:

DELETE DISTINCTROW Animals.*, Fruits.*
FROM Animals INNER JOIN Fruits ON Animals.Value = Fruits.Value;

However, when I run this, I receive the following error:

Could not delete from specified tables.

What am I doing wrong, and what can I do to fix this?

Answer 1

Without establishing a relationship to take advantage of a cascading delete, you're out of luck. The DELETE statement works on one table at a time (with cascading deletes, if any, carried out behind the scenes). You have no choice but to devise something to accomplish what you want. Perhaps, via a Macro, one could do something simplistic like this:

UPDATE Animals, Fruits SET Animals.Value="DELETED", Fruits.Value="DELETED" WHERE Animals.Value=Fruits.Value
DELETE Animals WHERE Animals.Value="DELETED"
DELETE Fruits WHERE Fruits.Value="DELETED"

Short of making a sophisticated VBA macro (and perhaps a temporary table), this is about as good as it gets.

As an aside, I don't believe it could be done even with a more heavy duty DB such as SQL Server or DB2; a DELETE upon a subquery or view still requires that the DB system can resolve it to a particular table.

I'm gonna guess that the error you got is a matter of locked rows (as a result of the INNER JOIN).