Delete query problems

[MajP]

Using Northwind I have this query

qryOrders

SELECT 
  [identifier] & [OrderID] AS ID, 
  "qryEmployees" & [EmployeeID] AS parentID, 
  [OrderID] & "  " & [orderdate] & " " & [companyname] AS  NodeText, 
  "qryOrders" AS identifier
FROM Customers 
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

I have a delete query

DELETE qryOrders.ID
FROM qryOrders
WHERE qryOrders.ID = "qryOrders10692"

In northwind there is referential integrity enforced between tables "Orders" and "OrdersDetail" and I have set this to allow cascading deletes.

If I manually delete from qryOrders I get the message that related records will get deleted and it works fine.

If I run the delete query I get the message "can not delete from specified table"

I thought that the delete query should run and you would just not get the warning about cascading changes. Any reason that this does not run as a delete query? Thanks

 

[cmmrfrds]

I don't think with the code you show that Access can determine definitively which table you want to delete the record from. You need to identify the table and probably best to use the primary key to identify the record to be deleted.

 

[MajP]

Thanks, but not sure if I understand what you are saying. The select query works fine. The delete query returns the single record, but can not delete it. The help item associated with the message is:

Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not be completed.

Possible causes:

You do not have permission to modify the table. To change your permissions assignments, see your system administrator or the table's creator.
The database was opened for read-only access. The database is read-only for one of these reasons:
You used the OpenDatabase method and opened the database for read-only access.
The database file is defined as read-only in the database server operating system or by your network.
In a network environment, you do not have write privileges for the database file.
In Microsoft® Visual Basic®, you used the Data control and set the ReadOnly property to True.
To delete the data, close the database, resolve the read-only condition, and then reopen the file for read/write access.

None of the above conditions are true, and I can manually delete from the select query.

 

[randy700]

A delete query will remove data from the table specified in the FROM clause. When you say DELETE FROM qryOrders, Access will look for a table name qryOrders. Unless you actually HAVE a table by that name, Access cannot delete from the specified (qryOrders) table.


Randy

 

[MajP]

Randy,
As the first post shows the original select query is called "qryOrders". It looks a little confusing because I am concatenating the query's name to the primary key. That is why the ID field actually has valuess that start with the name of the query. You are probably going to ask why. This query gets unioned with other queries for use in building treeviews. The identifier tells me which query the record originally came from.

 

[cmmrfrds]

When you manually delete that seems to remove the permissions problem. Also, at that point you are deleting the record from a DAO/ADO recordset where Access has mapped in sufficient information to determine/guess which record(s) to delete. I believe as Randy points out the original query is ambiguous on what to delete when run as a query.

 

[PHV]

In fact you wanted to do this ?
DELETE FROM Orders WHERE OrderID=10692

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886