CODEWITHSUNDEEP
sqlms-accessms-access-2010
rohrl77
rohrl77

Reputation: 3337

How to get delete query to work in MS Access

I have set up a subquery to select my records and then the delete query to perform the action. When I run it, I get an error message saying:

Could not delete from specified tables

Here my SQL code in the delete query:

PARAMETERS UnitID Short;
DELETE DISTINCTROW qry_exp_comments_select.*
FROM qry_exp_comments_select;

And the called subquery:

PARAMETERS UnitID Short;
SELECT tbl_Comments.*, tbl_Activity.ActivityID
FROM tbl_BusUnits INNER JOIN (tbl_Activity INNER JOIN tbl_Comments ON tbl_Activity.ActivityID = tbl_Comments.ActivityID) ON tbl_BusUnits.UnitID = tbl_Activity.UnitID
WHERE (((tbl_BusUnits.UnitID)<>[UnitID]));

Why won't the query work? I have tried to set it up as the one in the following thread: MS ACCESS delete query syntax combined with inner join problems

Upvotes: 1

Views: 598

Answers (1)

rohrl77
rohrl77

Reputation: 3337

In order to delete all the records in tbl_Comments and tbl_Activity all I had to do was change the relationships of the tables so that they have Cascade Delete activated.

Afterwards a simple delete query on the tbl_BusUnits also deleted all associated records in the other tables.

Code for the Delete Query as follows:

PARAMETERS UnitID Short;
DELETE DISTINCTROW tbl_BusUnits.UnitID
FROM tbl_BusUnits
WHERE (((tbl_BusUnits.UnitID)<>[UnitID]));

Upvotes: 1

Related Questions