Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't Delete (2K)

    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?

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Delete (2K)

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

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Delete (2K)

    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.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Delete (2K)

    Hmmmm <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

    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? <img src=/S/help.gif border=0 alt=help width=23 height=15>

    J

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't Delete (2K)

    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));
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •