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
- Start Microsoft Access and open the sample database Northwind Traders (NWIND.MDB).
- 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.
- From the Related Table box, select Order Details, and then select the Referential Integrity check box.
- Create a new query, add the Orders table, and choose Delete from the Query menu.
- 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.
- From the Query menu, choose Run.
- 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.
- 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.
