How to avoid the "Cannot delete from specified tables." in MS Access

Question

Here is the code that I am trying to run:

DELETE DISTINCTROW JHALL_REFERAL_ASSIGNMENTS.emp_id, JHALL_REFERAL_ASSIGNMENTS.ref_elem_id
FROM JHALL_REFERAL_ASSIGNMENTS
WHERE (((JHALL_REFERAL_ASSIGNMENTS.emp_id)=(select  b.emp_id from JHALL_REFERAL_ELEMENT a, JHALL_REFERAL_ASSIGNMENTS b, BSI_MARTS_D_EMPLOYEE c
    where C.FULL_NM = 'Employee'
    and A.REF_NAME ='Max Premium of 5,000'
    and A.REF_ELEM_ID = B.REF_ELEM_ID
    and B.emp_id = C.EMPLOYEE_KEY
)) AND ((JHALL_REFERAL_ASSIGNMENTS.ref_elem_id)=(select  a.ref_elem_id from   JHALL_REFERAL_ELEMENT a, JHALL_REFERAL_ASSIGNMENTS b, BSI_MARTS_D_EMPLOYEE c
    where C.FULL_NM = 'Employee'
    and A.REF_NAME ='Max Premium of 5,000'
    and A.REF_ELEM_ID = B.REF_ELEM_ID
    and B.emp_id = C.EMPLOYEE_KEY
  )));

Every time I try to run this in Access I get error 3086, "Cannot delete from specified tables." When trying to find information online I keep running into resolutions saying I should change the Unique Records field to "Yes" which I did but that did not solve my issue. I ran the same code (separating schema and table names with . instead of _) in Toad and it worked fine.

Solution 1

I reviewed several posts, including this one, to muddle through a similar delete. I used a query to distill the somewhat complex selection criteria down to a set of primary keys for the table targeted for record deletion.

I got the "Could not delete from specified tables" error and the "Specify the table containing the records you want to delete" error until I used:

delete distinctrow [Target_Table].* 
from [Target_Table] 
inner join [Criteria_Query] 
on [Criteria_Query].Index_PK = [Target_Table].Index_PK
where ( [Criteria_Query].Index_PK = [Target_Table].Index_PK )
;

This worked in Access 2013.

Solution 2

This is really an infuriating problem as even the code below, quoted from above, results in a "Cannot delete from specified tables" error, if Criteria_Query is actually a query and not table.

delete distinctrow [Target_Table].* 
from [Target_Table] 
inner join [Criteria_Query] 
on [Criteria_Query].Index_PK = [Target_Table].Index_PK
where ( [Criteria_Query].Index_PK = [Target_Table].Index_PK )
;

the solution is to first select the results of Criteria_Query into a table, tbl_Criteria_Query, and then use the table in the delete statement:

select *
into [tbl_Criteria_Query] 
from [Criteria_Query]
;

delete distinctrow [Target_Table].* 
from [Target_Table] 
inner join [tbl_Criteria_Query] 
on [tbl_Criteria_Query].Index_PK = [Target_Table].Index_PK
;

Solution 3

Select the IDs in a subquery and run delete on the table using in

Delete * from 
tbl_Delete

Where ID in (

Select id form table1
left join ..
left join ...

)

Comments

[Nathan Tuggy]

I had no problem with Steve's answer, even with a two-table criteria query without unique records turned on. Anything else that might have affected this?

[Nathan Tuggy]

Access usually does not have anything to do with VS, and Administrator permissions definitely won't help this at all.

[chrisis]

This doesn't appear to be an answer to the original question.

[Chris Meurer]

I encountered this and had to do this intermediary step as well. I think Access doesn't like using a non-updatable recordset in a JOIN on a DELETE. Another alternative, as described below, is to use a subselect in the where clause of the delete query, but in my experience this has been too slow to be useful once the delete-from table gets above a certain size (WHERE clause evaluated too many times)

[Rob]

This is great, because it also works with subqueries. Thanks!