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.

Thundercloud

Is there any foreign key or constraint attached to any of those rows? – Lampert

The employee_key, emp_id, and ref_elem_id are primary keys. Besides those there are no constraints. – Thundercloud

Does any of your primary keys have relationships? – Lampert

No, I didn't specify any relationships when developing it. – Thundercloud

Is your primary key autonumbered? – Lampert

Yeah, there are sequences generating the primary keys. – Thundercloud

You need to set up the all the selection criteria as a derived table (subquery) and then use IN or NOT IN as appropriate. I am fairly sure you won't be able to delete with that query. So DELETE FROM Table WHERE ID IN (SELECT ..) – Heliacal

That is how I tried to set it up the first time and still got the same error. I made a much simpler query for testing (DELETE * FROM JHALL_TEMP WHERE a = 1;) and got the same error so I'm fairly certain that the queries that I have been running aren't the issue (at least not the biggest one) but rather a setting somewhere in in access. Thanks for looking at my code though! – Thundercloud

Answer 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.

Guatemala

Answer 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
;

Amblyoscope

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? – Dittography

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) – Simp

Answer 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 ...

)

Hoahoactzin

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

Answer 4

I had the same error when using an MS Access front end and SQL server back end. I found that if I make my primary keys the same in SQL as in the local table in Access the problem was solved.

Outside

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

Answered the question title error for me thanks. – Radish

Answer 5

Just Run The Visual Studio (in Run As Administrator Mode).

Chazan

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

Answer 6

The following has worked for me designview->propertysheet->General->set unique records to "yes"

Toodleoo