Can’t Delete (2K)

Topic

New Reply

I have a customer database in which I want to delete all customers who do not have orders i.e. Customer table (1 side) orders table (many side). When I try to run a delete query containing the unmatched records, access tells me it can’t delete, or it tells me it has deleted but when I go to the table the records are still there. If I go to the customers table I can in fact delete records individually but this will take me forever to pick out all those without orders (few thousand customers)! I have opened with exclusive rights, the file is not read only, I have tried deleting all other relationships and removed referential integrity. How can I remove these records?

Reply | Quote

Replies

Can you post the SQL statement from the query that you are trying to use?

Reply | Quote

DELETE Customers.*, Orders.CustomerID
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.CustomerID) Is Null));

Customers and Orders are both tables.

Reply | Quote

Hmmmm

Your SQL looks fine to me, but in saying that, when I recreated the situation in a brand new database, I got exactly the same error message. I spose that Jet has some deep dark reason for not being able to do it (although the MS access help file explicitly states that DELETE works with one-to-many relationships).

I can offer a workaround. It isn’t very elegant, but I think that it would work.

Firstly, instead of using your query as a delete query, use it as a make table query (with all fields from the customers table). Call the table a temporary name. Run this, and this should now be a Customer table (with a temporary name), but with only Customers with Orders.

Now use a DROP statement in a query to delete the Customers table (DROP TABLE Customers)

Then use the DoCmd.Rename statement from Visual Basic to rename the temporary Customers table to the ‘live’ Customers table.

Yeah, messy I know, but it should work.

Anyone else know what is wrong with the DELETE query?

J

Reply | Quote

In Access 2000, you must use the DISTINCTROW keyword in delete queries or they will simply refuse to execute. Change your SQL to this:

DELETE DISTINCTROW Customers.*, Orders.CustomerID
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.CustomerID) Is Null));

Reply | Quote