Troubleshooting Delete Queries

[Phil Smith]

I find myself using workarounds for this way too often. A little advice
would be appreciated.

I have Three tables. I want to delete all entries from TableA where
TableB.Deleteme=yes and TableC.SignedOff = "yes". I get:

Cannot delete from specified table.

It seems like anytime I have more then one table involved, I can't delete.

I remove all of the other tables, and I can successfully delete.


Example query which fails. First version is exactly as teh Design View
builds it, the second is an editied version to remove waht appears to be
superfluous references. Both look the same in the Design Grid neither
run without generating the above error.

DELETE item_prebook_linkWRITEABLE.*,
item_prebook_linkWRITEABLE.prebook_id, prebook.name, item.active
FROM prebook INNER JOIN (item INNER JOIN item_prebook_linkWRITEABLE ON
item.item_id = item_prebook_linkWRITEABLE.item_id) ON prebook.prebook_id
= item_prebook_linkWRITEABLE.prebook_id
WHERE (((item_prebook_linkWRITEABLE.prebook_id)=150) AND
((prebook.name)="Special") AND ((item.active)="N"));


DELETE item_prebook_linkWRITEABLE.*
FROM prebook INNER JOIN (item INNER JOIN item_prebook_linkWRITEABLE ON
item.item_id = item_prebook_linkWRITEABLE.item_id) ON prebook.prebook_id
= item_prebook_linkWRITEABLE.prebook_id
WHERE (((item_prebook_linkWRITEABLE.prebook_id)=150) AND
((prebook.name)="Special") AND ((item.active)="N"));

 

[Jeanette Cunningham]

In access, the relationships between the tables (Referential integrity)
impacts the order for deleting records.
Delete records from a child table before you try to delete records from its
parent table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Spencer]

Avoid using Joins in Delete queries except in subqueries in the WHERE clause.

You might be able to use the following to accomplish the task. BACKUP your
data before testing this, so that you will have a way to recover if the wrong
records are deleted.

DELETE item_prebook_linkWRITEABLE.*
FROM prebook
WHERE item_prebook_linkWRITEABLE.item_id
IN (
SELECT item_prebook_linkWRITEABLE.ItemID
FROM prebook INNER JOIN
(item INNER JOIN item_prebook_linkWRITEABLE
ON item.item_id = item_prebook_linkWRITEABLE.item_id)
ON prebook.prebook_id = item_prebook_linkWRITEABLE.prebook_id
WHERE item_prebook_linkWRITEABLE.prebook_id=150
AND prebook.name="Special"
AND item.active="N")

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

 

[Paul LeBlanc]

I am having similar problems
I have a macro that runs a series of update, apend and delete querys using
SQL statements. It hangs on the last delete query which is the same as all
the other delete queries but on a different table.
A sample of the one that works is this

DELETE TblLayoutActual.*
FROM TblLayoutActual INNER JOIN Tbl8InFS ON TblLayoutActual.PK = Tbl8InFS.PK;

When that style hangs I tried this

DELETE TblLayoutActual.*, TblLayoutActual.PK
FROM TblLayoutActual, TblHibQuickStart
WHERE (((TblLayoutActual.PK)=(TblHibQuickStart!PK)));

eliminating the join on PK {Primary Key}
I get the same "cannot delete from specified table" as John

 

[John Spencer]

Try using something like the following:

DELETE TblLayoutActual.*
FROM TblLayoutActual
WHERE TblLayoutActual.PK IN
(SELECT Tbl8InFS.PK
FROM Tbl8InFS)

In some cases you can use DISTINCTROW predicate(?) to make this syntax work
DELETE DISTINCTROW TblLayoutActual.PK
FROM TblLayoutActual INNER JOIN Tbl8InFS
ON TblLayoutActual.PK = Tbl8InFS.PK;

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

 

[Phil Smith]

What exactly is a "child" table and "Parent" table in Access? I
undersstand the concept, but not the definition as Access sees it. Here
is the problem I tend to run into a lot. A lot of the pices I work with
use THREE tables, An example follows:
Item Pricing.

ItemTable contains
Item#, Item Description.
1 box
2 bag
3 basket


PriceTypeTable Contains
PriceType#,Price Description
1 Wholesale
2 MSRP
3 StandardDiscount

ItemPriceLink Contains
Item# PriceType# Value
1 1 $4.00
1 2 $8.00
1 3 $6.00
2 1 $1.00
2 2 $2.00
2 3 $1.50



So to see all the wholesale prices, I need to reference those threee
tables. The prices are kept in ItemPriceLink, but WHICH price it is is
kept in PriceTypeTable.

A lot of the database I am working with is built this way. I see the
advantage of this type of design. If you want to add another price
level, say "International" or "Employee Price", is is as simple as
adding a record to PriceTypeTable, at which you have a new price you can
add to whatever items you choose.

But, it adds a level of complexity to the whole thing. It results is,
(conceptually at least,) Two parent tables for each child. Without both
ItemTable and PriceTypeTable, ItemPriceLink is useless.

Any insight you can give to this problem?

Phil

 

[Jeanette Cunningham]

The child table is ItemPriceLink .
It use the primary key of ItemTable as one of the fields in each row.
Because you have set referential integrity between ItemTable and
ItemPriceLink, you must delete the related records in ItemPriceLink before
you can delete from ItemTable.

To look at in reverse, you couldn't use an Item to put in ItemPriceLink if
that Item wasn't already in ItemTable.
So in the same way, you can't delete that Item from ItemTable if it is being
used in ItemPriceLink.
Once you delete the item from all the rows in ItemPriceLink that use that
item, then you can delete that item from ItemTable.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Phil Smith]

That is pretty much as I expected, except for one thing. ItemPriceLink
records is the only thing I would ever want to delete, (along with
several other *link records, where the tables follow the same basic
format, and these are the most problematic for me.

At this point I am looking for a consistent method to delete records
such as this, and am seriously considering building select queries,
which generate SQL code to delete each record as the only output, and a
function which will take that output, and execute each record/sql
statement one at a time.

Phil

 

[Jeanette Cunningham]

Yes,
I often use this technique.
I have a module for all the sql for the delete queries and the checks to see
if that record can be deleted.
Whenever I wish to delete a record, I can just call up the delete code for
that type of record.
I also use a custom function that runs an action query string passed to it
and reports back if the query ran successfully or not.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia