cannot delete from "specified tables"

[NewtoComputing]

HI.

I have used an SQL union query to join 2 tables and created a table from
this query.
Then i've used a totals query to identify duplicate records based on date.
Then i've created a delete query to get rid of the duplicate records.

I've followed all instructions thoroughly and there are no relationships
between the tables in the delete query and other tables. I also have
permission to access and amend any part of the database yet I am still faced
with the message that I "can't delete from specified tables".

What am I missing?

Thanks

 

[Ken Snell \(MVP\)]

Post the SQL statement of the delete query; chances are that you'll need to
reformat the query so that Jet / ACCESS will know from which table to delete
the records. Are you including the totals query in the delete query as a
source table, and joining to it?

 

[NewtoComputing]

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]));

P.s. thanks for the fast reply

 

[John Spencer]

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.

PERHAPS you can use
DELETE DISTINCTROW ACADMASTERUPDATED.NEWID,
FROM ACADMASTERUPDATED INNER JOIN totalACADMASTERUPDATEDtbl ON
ACADMASTERUPDATED.NEWID = totalACADMASTERUPDATEDtbl.NEWID
WHERE (((ACADMASTERUPDATED.NEWID)=[totalACADMASTERUPDATEDtbl].[NEWID]) AND
((ACADMASTERUPDATED.LAST_UPDATED)<>[totalACADMASTERUPDATEDtbl].[MaxOfLAST_UPDATED]));

If that doesn't work then try
DELETE DISTINCTROW ACADMASTERUPDATED.NEWID
FROM ACADMASTERUPDATED
WHERE ACADMASTERUPDATED.NEWID IN
(SELECT Tmp.NEWID
FROM ACADMASTERUPDATED As Tmp INNER JOIN totalACADMASTERUPDATEDtbl
ON Tmp.NEWID = totalACADMASTERUPDATEDtbl.NEWID
WHERE Tmp.LAST_UPDATED)<>[totalACADMASTERUPDATEDtbl].[MaxOfLAST_UPDATED])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

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]));

P.s. thanks for the fast reply

Post the SQL statement of the delete query; chances are that you'll need to
reformat the query so that Jet / ACCESS will know from which table to delete
the records. Are you including the totals query in the delete query as a
source table, and joining to it?