Specify the table containing the records you want to delete

  • Thread starter Thread starter Stewart Berman
  • Start date Start date
S

Stewart Berman

Access 2007

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?
 
Let me get this right. You want to delete all of the records from Table1
where there is a match between the primary key fields in table 1 and table2.

My recommendation would be:

DELETE Table1.*
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2)
 
Try the following which sometimes works
DELETE DISTINCTROW Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.PrimaryKeyField = Table2.PrimaryKeyField;

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
 
I already tried that -- same error.

DELETE Table1.*
FROM Table1, Table2
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);

Results: Could not delete from the specified tables
 
I already tried that.

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 tried
DELETE Table1.*
FROM Table1, Table2
WHERE Table1.PrimaryKeyField IN (SELECT PrimaryKeyField FROM Table2);

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 missed the absence of Table2 in the FROM clause. I tried it the way you specified it and it
worked.

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.
 
Yeah, the IN clause in notoriously slow, but generally works.

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
 
I've been told - never tested this - that using an exists clause is faster.
That query would look like the following. It may or may not be faster, but it
is something you could test.

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)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Access 2007

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?
Click to expand...

When I looked at your post it finally donned on me that I had added "attachment" type fields to my table and those fields were wrecking havoc on my linked delete queries. Once I deleted the attachment fields the delete queries worked great (I didn't really need them! I know this doesn't help you much but I wanted to say it helped solve a big mystery for me. I'm assumingthat you can't just arbitrarely delete attachment fields because of the way they are structed but I'm not sure. You'd probably have to just restructure the data. Thanks!
 
Back
Top