Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query doesnt work (Access 2000)

    Delete query doesnt work

    I have a problem with the following delete query:

    DELETE [Orders].[OrderID], *
    FROM Orders
    WHERE (((Orders.OrderID) In (SELECT Orders.OrderID FROM Orders LEFT JOIN Orders1 ON Orders.OrderID = Orders1.OrderID WHERE ((Orders1.OrderID) Is Null))));

    The message i receive is the following:

    Microsoft Access cant delete the records in the delete query due to key violations and 0 recors due to lock violations.


    Orderid is a primary key.Is this the reason? And can i perform the delete query?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Delete query doesnt work (Access 2000)

    I have just built a database to test this, and quess what, it works.
    There's nothing wrong with your delete query.
    I have OrderID as the primary key, so that's not it.
    I haven't been much help, other than it works on mine.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query doesnt work (Access 2000)

    This problem will occur if
    1. <LI>The Orders table has a relation to another table, with Orders on the "one" side of the relation.
      <LI>Referential integrity has been set for this relation.
      <LI>Cascading deletes have *not* been set for this relation.
      <LI>The table on the "many" side of the relation contains records linked to records in Orders that you want to delete.
    Deleting the records in Orders would leave some records in the other table as poor little orphans. Access thoughtfully prevents this.

    If it is OK to delete the related records, open the Relations window, double click the relation between Orders and the other table, and check the "Cascading deletes" box. Note: think hard before you do this - are you sure you want this?
    Otherwise, you'll have to add the other table to your delete query, with another outer join, to select only those records in Orders that have no related records in the other table.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query doesnt work (Access 2000)

    Dear Hans,

    Thank you so much for your answer.I think you have a perfect explanation as to why it happens.I understand i must delete at first the other table of the
    relationship.The other table of the relation is called Order Details. So i must at first delete here all the rows from the table order details, where
    the orderid from the table order details1 = 0


    DELETE [order details].[OrderID], *
    FROM [order details]
    WHERE ((([order details].OrderID) In (SELECT [order details].OrderID FROM [order details] LEFT JOIN [order details1] ON [order details].OrderID = [order

    details1].OrderID WHERE (([order details1].OrderID) Is Null))));

    When trying to execute the above query, no error occurred, but it showed that i will delete 0 records.Could you please see
    perhaps i am wrong somewhere with the logic of my delete commands.


    In order to help me i think i should explain once again what i need. The tables orders and order details are constant tables in the my database.
    Someitems i import the tables orders1 and order details1 into the database and append them.It works.However, sometimes an order has been deleted in the

    tables orders1 and order details1 and i need to delete at first those rows that do not exist in the table orders1.
    Best regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete query doesnt work (Access 2000)

    A record in Order Details without a corresponding record in Orders is useless and should not exist. So in this case, I would recommend turning on Cascading Deletes in the relation between Orders and Order Details. If you delete records from Orders, the related records in Order Details will be deleted automatically, so there is no need for a separate delete query that acts on Order Details.

Posting Permissions

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