CODEWITHSUNDEEP
sqlms-accessruntime-errorsql-delete
user1466887
user1466887

Reputation: 53

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

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.

Upvotes: 5

Views: 29640

Answers (6)

Iron excel
Iron excel

Reputation: 1

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

Upvotes: 0

Sean
Sean

Reputation: 31

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.

Upvotes: 1

Bhanu Sinha
Bhanu Sinha

Reputation: 1776

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

)

Upvotes: 3

Deep Biswas
Deep Biswas

Reputation: 17

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

Upvotes: 0

David J. Heinrich
David J. Heinrich

Reputation: 41

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
;

Upvotes: 4

Steve
Steve

Reputation: 121

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.

Upvotes: 12

Related Questions