Converting an Access elect query to a delete query.

[msowards]

delete Temp_Updates_list_Table.*
FROM (SELECT Temp_Updates_list_Table.* 
FROM Temp_Updates_list_Table 
WHERE Temp_Updates_list_Table.Actiontaken="MainRecord deleted")  
AS Tmp_B LEFT JOIN Temp_Updates_list_Table 
ON Tmp_B.Main_RecID=Temp_Updates_list_Table.Main_Recid
WHERE (((Temp_Updates_list_Table.Actiontaken)<>"MainRecord deleted"));

This query as a select query produces a result of records I want to delete. So I changed the SELECT to a delete and tried to execute it. Access complained that it "Could not delete from Specified tables."
The "help" button on the error popup was less than helpful (error 3086).
Anyone have an idea on converting this SQL to work with MS Access SQL?

 

[vbaInet]

What is the exact error message? Does it disply any records in Datasheet View?

You probably need to give the table name in your subquery an alias as well.

 

[msowards]

The error message given is "Quoated" in the post -- "Could not delete from Specified tables."
I've already written a version using an alias for the second table name with the same results and a version with the first table aliased. I've encountered this before with a delete query containing a Group By which Access refuses to execute as a delete query.

 

[vbaInet]

Can you upload a stripped down version of your db? I want to run it myself.