PRACC9311: Erroneous "Lost Records" Error on Delete Query

PSS ID Number: Q106301
Article last modified on 11-05-1993

1.00 1.10
WINDOWS

---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0 and 1.1
 - Microsoft Access Distribution Kit version 1.1
---------------------------------------------------------------------

SYMPTOMS
========
When you attempt to delete records from a table that has referential
integrity with another table, you get the following error message
   <n> records were lost due to key violations...
where <n> is the number of records in the dynaset. This error appears
even though no records were lost or deleted from the table.

STATUS
======
Microsoft has confirmed this to be a problem in Microsoft Access
versions 1.0 and 1.1. We are researching this problem and will post
new information here in the Microsoft Knowledge Base as it becomes
available.

MORE INFORMATION
================
Referential integrity rules preserve defined relationships between
tables when you enter or delete records. If you enforce referential
integrity, Microsoft Access prohibits you from adding records to a
related table for which there is no primary record, changing values in
a primary table that would result in orphan records in a related
table, or deleting records from a primary table when there are
matching related records.
When you attempt to delete records from a related table that has
referential integrity with a primary table, Microsoft Access will not
delete the records. You will instead get the erroneous error message
stated above.  

Steps to Reproduce Problem
--------------------------
1. Start Microsoft Access and open the sample database Northwind
   Traders (NWIND.MDB).
2. Establish referential integrity between the Orders and Order
   Details tables. To do this, select the Orders table in the table
   list and then choose Relationships from the Edit menu.
3. From the Related Table box, select Order Details, and then select
   the Referential Integrity check box.
4. Create a new query, add the Orders table, and choose Delete from
   the Query menu.
5. Drag the asterisk from the Orders table to the Query-by-Example
   (QBE) grid. This sets up the query to delete all records from the
   Orders table.
6. From the Query menu, choose Run.
7. You will get the message "1078 row(s) will be deleted." Choose the OK
   button.
   The query will run and you will get the error message:
      Errors were encountered: The contents of fields in 0 records
      were deleted, 1078 records were lost due to key violations,
      and 0 records were locked and couldn't be modified. Proceed
      anyway?
   However, no records were lost or deleted.
8. Close Microsoft Access without saving the changes to NWIND.MDB.

Additional reference words: 1.00 1.10 
KBCategory:
KBSubcategory: GnrlNw GnrlRefrint QryMaktbl
Copyright Microsoft Corporation 1993.