ACC: Update or Delete Query Fails Without Generating Error

ID: Q117163

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

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

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.

MORE INFORMATION

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.

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.

Open a new module and enter the following sample code:


      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

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.

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.

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.

Keywords : kbusage QryMktbl
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbbug