Delete Query - Could not delete from specified tables 1

[saustin]

Hi All,
Used the Access 2000 design area to create a Delete Query. When Previewing the query I see the exact records I want to delete. When running the query the error "Could not delete from specified tables" appears.
Am following the exact steps from help and MS Kb. Any ideas ?

Here is the sql ...
DELETE ILS.*, dbo_tblInvoiceDetail.PARTID, dbo_tblInvoice.DTINVOICE
FROM (ILS INNER JOIN dbo_tblInvoiceDetail ON ILS.PARTID = dbo_tblInvoiceDetail.PARTID) INNER JOIN dbo_tblInvoice ON dbo_tblInvoiceDetail.INVOICEID = dbo_tblInvoice.INVOICEID
WHERE (((dbo_tblInvoice.DTINVOICE)>(Now()-365)));

This query should delete records in table ILS which is related to table InvoiceDetail based on an Invoice date in table Invoice (which InvoiceDetail is related to).

Any ideas greatly appreciated.

Thanks, Steve.

Steve Austin
MIS Department
Helicopter Support, Inc.
(p): (203)795-6051 Ext. 3378
(f): (203)799-5020

 

[Aivars]

Hi, Steve!

Change SQL clause and then I think selected records from table ILS will be deleted.

DELETE ILS.*
FROM (ILS INNER JOIN dbo_tblInvoiceDetail ON ILS.PARTID = dbo_tblInvoiceDetail.PARTID) INNER JOIN dbo_tblInvoice ON dbo_tblInvoiceDetail.INVOICEID = dbo_tblInvoice.INVOICEID
WHERE (((dbo_tblInvoice.DTINVOICE)>(Now()-365)));

Aivars

 

[saustin]

Hi Aivars,
Many thanks for the reply however the error still
persists. Preview shows the records to be deleted but runing the query produces the "Could not delete from specified tables" error.

Steve.

 

[Guest_imported]

Hi,

You may have checked this, but...
Are the records participating in a relationship. If so, are they primary records, or foreign records. You can not delete a primary record that has matching related records in a foreign table. This assumes that "Enforce Referential Integrity" has been activated. You must first delete the related records.

Hope it helps

 

[saustin]

Hi Janya,
Thanks for the input. Did not "Enforce Referential Integrity". The issue seems to be resolved by deleting in a subquery.
Steve.

 

[AirZebra]

Steve,

Can I ask how you resolved this? I have a similar problem when I am trying to run a delete query. The query is based on another query, and displays the records I want to get rid of in the datasheet view, but brings up the same error message when I try to action the query.

Alan

 

[Megahurtz]

Could not delete from specified tables but you can "SELECT" the right records to delete


The delete query contains multiple tables, and the UniqueRecords property is set to No

RESOLUTION
Set the UniqueRecords property of the query to Yes . To set the UniqueRecords property of the query to Yes , follow these steps:

Open the delete query in Design view.
On the View menu, click Properties
easily performed by right click in space below your design grid qry fields
Set the UniqueRecords property to Yes .
Save the query, and then run...

This one annoyed me for a while

MeGaHuRtZ

here's a link to more info

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q240098