help...Can't delete from specified tables

  • Thread starter Thread starter NewtoComputing
  • Start date Start date
N

NewtoComputing

I keep getting the "can't delete from specified tables" message.
I've tried using the totals query as it is, as well as using a table composed
of the records from the totals query.

The SQL for the latter is;

DELETE ACADMASTERUPDATED.*, ACADMASTERUPDATED.NEWID,
ACADMASTERUPDATED.LAST_UPDATED
FROM ACADMASTERUPDATED INNER JOIN totalACADMASTERUPDATEDtbl ON
ACADMASTERUPDATED.NEWID = totalACADMASTERUPDATEDtbl.NEWID
WHERE (((ACADMASTERUPDATED.NEWID)=[totalACADMASTERUPDATEDtbl].[NEWID]) AND
((ACADMASTERUPDATED.LAST_UPDATED)<>[totalACADMASTERUPDATEDtbl].[MaxOfLAST_UPDATED]));
 
Do it in 2 steps.
Step 1 is to get a list containing NewID for AdadmasterUpdated that shows
the NewID for each record you want to delete.
Save this query, when you open this query you should only be able to see
NewID and all the other fields are not showing.
Now create a new query. Join the saved query from above to AdadmasterUpdated
on NewID and make this the delete query.


Jeanette Cunningham -- Melbourne Victoria Australia
 
I keep getting the "can't delete from specified tables" message.
I've tried using the totals query as it is, as well as using a table composed
of the records from the totals query.
Click to expand...

No Totals query, nor any query containing a join to a Totals query, is ever
updateable. You may be able to delete these records using a Subquery instead.
Try

DELETE ACADMASTERUPDATED.*
FROM ACADMASTERUPDATED
WHERE NEWID IN(
SELECT NEWID FROM totalACADMASTERUPDATEtbl
WHERE
ACADMASTERUPDATED.NEWID = totalACADMASTERUPDATEDtbl.NEWID
ACADMASTERUPDATED.LAST_UPDATED)<>[totalACADMASTERUPDATEDtbl].[MaxOfLAST_UPDATED];
 
Back
Top