I am getting a "Specify the table containing the records you want to delete" error when trying to
delete rows in a linked table containing an attachment field using an inner join.
I have reproduced the problem using a table structure with two fields:
PrimaryKeyField Autonumber
Picture Attachment
The query is:
DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField;
With the attachment field in either table the query fails. If I remove the Attachment field from
both tables the query works.
Is this a known problem and if so how do I get around it?
My recommendation would be:
DELETE Table1.*
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2)
----
HTH
Dale
Or use Dale's method which should always work.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
DELETE Table1.*
FROM Table1, Table2
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);
Results: Could not delete from the specified tables
DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField;
Gave the same error:
Specify the table containing the records you want to delete
You were told to try
DELETE Table1.*
FROM Table1
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);
NOTE there is only ONE table in the FROM clause of the Delete query.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I still need to get a fix for the join not working as the work around is significantly slower than a
join on two primary keys but at least I have something to use for now.
Thanks.
You could also try:
DELETE Table1.*
WHERE DLOOKUP("PKField", "Table2", "[PKField] = " & Table1.PkField) IS NOT
NULL
I have, at times, added a column (IsSelected - Yes/No) to Table1. With this
structure, you should be able to write an update query and set IsSelected to
true for those records that match. Then do a Delete query where IsSelected.
This may be quicker than using the In clause.
BTW, are the fields you were using actually PKs? Are they autonumber, or
are they self generated? If not true PKs, are they indexed?
Dale
"Stewart Berman" <sabe...@nospam.nospam> wrote in message
news:qqn7b5hjnhnga0g7t...@4ax.com...
It might be faster if there were only a few records in Table1 and a lot of
records in table2. And if the reverse were true it might be considerably slower.
DELETE Table1.*
FROM Table1
WHERE Exists
(SELECT *
FROM Table2
WHERE Table2.PrimaryKeyField = Table1.PrimaryKeyField)