CODEWITHSUNDEEP
ms-accessms-access-2007sql-delete
Mark Sowards
Mark Sowards

Reputation: 171

MS Access delete Query

I have query in MS Access that produces a correct records result, but Access refuses to run the Query as a delete query?
Can any one help me rewrite this query to run in access.

Delete Table_A.*
FROM (SELECT Table_A.Main_RecID, Table_A.Fld_Unique_ID, Table_A.Actiontaken FROM Table_A 
WHERE Table_A.Actiontaken="MainRecord deleted")  AS Tmp_B 
LEFT JOIN Table_A ON Tmp_B.Main_RecID=Table_A.Main_Recid
WHERE (((Table_A.Actiontaken)<>"MainRecord deleted"));

If the "Delete" is replaced by a select or I ask for a datasheet view the Query produces what I would expect. Which is a list of the records in the table that have the same Main_RecID as records with Actiontaken field = "MainRecord deleted" but do not have their Actiontaken field equal to "MainRecord deleted".

Access responds with the message "Could not delete from specified tables."

Upvotes: 1

Views: 1563

Answers (1)

HansUp
HansUp

Reputation: 97101

I started with this data in Table_A ...

Fld_Unique_ID Main_RecID Actiontaken
            1          1 MainRecord deleted
            2          1 
            3          2 MainRecord deleted
            4          2 something else
            5          3 something else

Note Actiontaken is Null in second row (where Fld_Unique_ID = 2).

Executing the DELETE statement below leaves this data in the table.

Fld_Unique_ID Main_RecID Actiontaken
            1          1 MainRecord deleted
            3          2 MainRecord deleted
            5          3 something else

DELETE
FROM Table_A
WHERE
    (
           Actiontaken<>'MainRecord deleted'
        OR Actiontaken Is Null
    )
    AND
    (
        DCount("*", "Table_A",
            "Main_RecID = " & Main_RecID
            & " AND Actiontaken='MainRecord deleted'") > 0
    );

If that's not what you're after, please show us sample data before and after DELETE.

Upvotes: 0

Related Questions