Microsoft KB Archive/269379

= ACC2000: Unexpected Error with Cascading Deletes in an Access Project =

Article ID: 269379

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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:  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. Click Code on the View menu.  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.



Steps to Reproduce the Behavior
  Open any Access project, and then create the following tables: <pre class="fixed_text">   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) </li> Save and close each table.</li> Right-click the OneX table, and then click Triggers. Click New.</li>  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 </li> Close the trigger window, and then open the OneX table in Datasheet view.</li> Enter the following information in the OneX table:

Bruce

Hal

Barry

</li> Enter the following information in the ManyX table:

Bruce

Bruce

Bruce

Barry

Hal

Hal

Hal

</li> Close the ManyX table.</li> Open the OneX table in Datasheet view.</li> 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.</li></ol>

Note that you see the behavior that is described in the &quot;Symptoms&quot; section of this article.

Additional query words: pra

Keywords: kberrmsg kbbug kbnofix KB269379

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.