ACC2000: Unexpected Error with Cascading Deletes in an Access Project (269379)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q269379
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

In an Access project, when you try to delete a record from a table, you receive one of the following error messages.

If you are using SQL Server:
Insufficient or incorrect key column information; too many rows affected by update.
If you are using the Microsoft Data Engine (MSDE):
Key column information is insufficient or incorrect. Too many rows were affected by update.
Then you see that the record that you deleted still appears in the table. After you refresh the table or close and then re-open the table, you see that the record was actually deleted.

CAUSE

The table that you deleted the record from contains a cascading delete, and there are two or more matching records in the other table or tables that the trigger acts upon.

RESOLUTION

To work around this behavior, follow these steps:
  1. Create a form based on the one-sided table. If you want to, you can make the form default to Datasheet view in the form's property sheet by changing the DefaultView property on the Format tab.
  2. Click Code on the View menu.
  3. Type or paste the following code:
    Option Compare Database
Option Explicit

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Me.Requery
End Sub
Now when you delete a record in the form, you will not receive an error message, and the screen will be refreshed.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open any Access project, and then create the following tables:
        Table: OneX
    -----------------------------------
    Field Name: ID (set as Primary Key)
     Data Type: uniqueidentifier

    Field Name: oTestName
     Data Type: char (10)

    Table: ManyX
    -----------------------------------
    Field Name: ID (set as Primary Key)
     Data Type: uniqueidentifier

    Field Name: mTestName
     Data Type: char (10)
  2. Save and close each table.
  3. Right-click the OneX table, and then click Triggers. Click New.
  4. In the trigger window, delete all the text, and then type the following code:
    Create Trigger OneX_CascDel
On OneX
For Delete
As
delete ManyX FROM ManyX, deleted  
where ManyX.mTestName = deleted.oTestName
  5. Close the trigger window, and then open the OneX table in Datasheet view.
  6. Enter the following information in the OneX table:

    <uniqueidentifier> Bruce
    <uniqueidentifier> Hal
    <uniqueidentifier> Barry

  7. Enter the following information in the ManyX table:

    <uniqueidentifier> Bruce
    <uniqueidentifier> Bruce
    <uniqueidentifier> Bruce
    <uniqueidentifier> Barry
    <uniqueidentifier> Hal
    <uniqueidentifier> Hal
    <uniqueidentifier> Hal

  8. Close the ManyX table.
  9. Open the OneX table in Datasheet view.
  10. Delete the record that contains the name Hal, and click Yes when you are prompted if you are sure that you want to delete the record.
Note that you see the behavior that is described in the "Symptoms" section of this article.
Modification Type:MajorLast Reviewed:6/25/2004
Keywords:kbbug kberrmsg kbnofix KB269379
©2004 Microsoft Corporation. All rights reserved.