Delete Query error 3086

[Guest]

I'm trying to do what I thought was a simple delete query, and I'm running
into an error 3086 "could not delete from specified tables". I have the
appropriate access to the table, so the on-line help in Access is not very
helpful.

Here's what I'm trying to do:

Delete all records in tblShipOrders_Summary where GL_GL_WORKCENTER_ID,
GL_PROD_ID and GL_SHIP_TO_CUST_ID equals that in SELECT DISTINCT
GL_GL_WORKCENTER_ID, GL_PROD_ID, GL_SHIP_TO_CUST_ID
FROM tblShipOrders_No_Invoices

Please help!!!

 

[John Vinson]

I'm trying to do what I thought was a simple delete query, and I'm running
into an error 3086 "could not delete from specified tables". I have the
appropriate access to the table, so the on-line help in Access is not very
helpful.

Here's what I'm trying to do:

Delete all records in tblShipOrders_Summary where GL_GL_WORKCENTER_ID,
GL_PROD_ID and GL_SHIP_TO_CUST_ID equals that in SELECT DISTINCT
GL_GL_WORKCENTER_ID, GL_PROD_ID, GL_SHIP_TO_CUST_ID
FROM tblShipOrders_No_Invoices

Please help!!!

Do you have a unique Index on the combination of the three fields? If
so, try a query Joining tblShipOrders_Summary to
tblShipOrders_No_Invoices by all three fields:

DELETE tblShipOrders.Summary.*
FROM tblShipOrders.Summary INNER JOIN tblShipOrders_NoInvoices
ON tblShipOrders.Summary.GL_GL_WORKCENTERID =
tblShipOrders_NoInvoices.GL_GL_WORKCENTERID
AND tblShipOrders.Summary.GL_PROD_ID=
tblShipOrders_NoInvoices.GL_PROD_ID
AND tblShipOrders.Summary.GL_SHIP_TO_CUST_ID =
tblShipOrders_NoInvoices.GL_SHIP_TO_CUST_ID;


John W. Vinson[MVP]

 

[Guest]

In the tblShipOrders_Summary table, the combination of the 3 fields does not
identify a unique record - when I join the tables as you suggested using a
select query, it correctly identifies 28 records. However, converting the
select query to a delete query gives me the "could not delete from specified
tables" error.

 

[Michel Walsh]

Hi,


Try adding the word DISTINCTROW right after the DELETE and before the table
name from which we delete (over the join).


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

That did it - Thanks!

Hi,


Try adding the word DISTINCTROW right after the DELETE and before the table
name from which we delete (over the join).


Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

Try adding the word DISTINCTROW

D'oh! <slapping forehead>

Thanks Michel. Of course... I knew that... <g>

John W. Vinson[MVP]

 

[Michel Walsh]

Hi,



I often forget it myself, but when I get the error message, I then
"d'oh" remember it.


Vanderghast, Access MVP

 

[Guest]

Just for my continued education... If you are using Design View and not SQL
to write the query where would you "tell" it to add the DISTINCTROW command?

I was having the identical problem, and adding DISTINCTROW solved it

 

[Guest]

Nevermind the previous question... I see you have to choose "Yes" for Unique
Records in the properties box.

 

[Guest]

Thanks, Robin, I use the design view and I was facing the same problem with a
delete query. Your note helped. Also if you set a primary key in one of the
tables that one is joining in the delete query, that does it too. But since I
am using this delete query in a macro, seting the 'unique records' property
to 'yes' is more helpful