'Cannot delete from specified table' message

[Dorian]

Why does this query work fine, yet when I replace 'SELECT C.ID' WITH 'DELETE
C.*', I get 'cannot delete from specified table'. I clicked on Help and none
of the reasons listed apply.

SELECT C.ID
FROM tblClient AS C LEFT JOIN tblCSTickets AS T ON C.ID = T.ClientID
WHERE T.ClientID IS Null;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer (MVP)]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Try using the DISTINCTROW option. It often works when more than one table is
involved in the from clause of a delete query.

DELETE DISTINCTROW C.ID
FROM tblClient AS C LEFT JOIN tblCSTickets AS T ON C.ID = T.ClientID
WHERE T.ClientID IS Null;

If that doesn't work try this one which should always work
DELETE
FROM tblClient
WHERE ClientID in
(SELECT C.ID
FROM tblClient AS C LEFT JOIN tblCSTickets AS T ON C.ID = T.ClientID
WHERE T.ClientID IS Null)



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