Microsoft KB Archive/117163

= ACC: Update or Delete Query Fails Without Generating Error =

Article ID: 117163

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q117163



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
When you run an update or delete query in code using the Execute method, some records are not modified or deleted, and no error message is generated.



CAUSE
Those records that were not modified or deleted were locked when the action query was run. A record is locked whenever it is being edited, whether that editing is done through the user interface, from code, or by an action query.



RESOLUTION
In Microsoft Access version 2.0, use the DB_FAILONERROR argument when you use the Execute method to run an action query from code. This switch issues a rollback and generates an error when locking conflicts occur, resulting in the cancellation of all updates. The following is a code example showing the use of the DB_FAILONERROR argument and error trapping:

  Option Explicit

Function RunUDQuery Dim db As Database, qdef As QueryDef Set db = CurrentDB Set qdef = db.QueryDefs("UDQuery") On Error GoTo Errorhandler qdef.Execute DB_FAILONERROR Exit Function

  Errorhandler:

MsgBox "Update Failed " & Err & " " & Error Exit Function

End Function

In Microsoft Access version 1.x, create a dynaset in Access Basic code that includes all the records that should be modified. Within transactions (BeginTrans and CommitTrans), modify each record in the dynaset until all the records are updated. This technique will generate an error that you can trap if a locking conflict occurs. You can then issue a rollback on the transaction and try to update the record again until the update succeeds.



STATUS
This behavior no longer occurs in Microsoft Access version 7.0.



Steps to Reproduce Problem
 Start Microsoft Access and open any database.  Create a new table called Table1. Add a text field called Info to the table. View the table in Datasheet view and add the following records:

     Info ddd aaa ccc ddd ddd eee  Create a new query based on Table1. Drag the Info field to the query grid. Type "ddd" (without the quotation marks) in the Criteria field. Save the query as Query1. Run the query and note that three records are displayed. Close the query.</li> Create an update query based on Table1. Drag the Info field to the query grid. Type zzz in the Update To row, and type ddd in the Criteria row. Save the query as UDQuery.</li>  Open a new module and enter the following sample code:

<pre class="fixed_text">     Option Explicit 'If not already present. Function RunUDQuery Dim db As Database, qdef As QueryDef Set db = CurrentDB Set qdef = db.OpenQueryDef("UDQuery") qdef.Execute End Function </li> Create a new form based on Table1. Add a text box based on the Info field to the form. Add a command button to the form, and set the button's OnClick property to "=RunUDQuery" (without the quotation marks). Save the form as Form1.

NOTE: In Microsoft Access version 1.x, the OnClick property is called the OnPush property.</li> View Form1 in Form view and type "xxx" (without the quotation marks) in the Info text box. Note that the record selector changes from an arrow to a pencil, indicating that the record is being edited and is locked. Click the command button on the form to run the update query. Note that no error message is generated.</li> Press ESC to undo your typing in the form. Open Query1 and note that one record still matches the "ddd" criteria. This record was not updated by the update query because the record was being edited when the update query was run.</li></ol>

Keywords: kbbug kbusage KB117163

-

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

© Microsoft Corporation. All rights reserved.