ACC2000: Action Query Commits Changes to Data When Cancelled

• Microsoft Access 2000

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you run an action query and respond to the prompt to commit the changes by clicking No, the changes are committed anyway.

CAUSE

The action query's UseTransaction property is set to No.

RESOLUTION

Set the UseTransaction property to Yes before running the action query.

MORE INFORMATION

The UseTransaction property specifies whether an action query runs as a single transaction. When the UseTransaction property is set to Yes, the query results are stored in a cache or temporary database and are not written into the current database until you confirm that you want to commit the changes. When the UseTransaction property is set to No, the results are written immediately to the current database; the query runs much faster because it is not wrapped in a single transaction. However, you cannot cancel the changes that the query has made, even if you click No when prompted to commit the changes.

If you create a query in the Microsoft Access user interface by opening a new query in Design view, the default value of the UseTransaction property is Yes. However, when you use Data Access Objects (DAO) to create a QueryDef in Visual Basic for Applications code, the default value of the UseTransaction property is No.

In the following example, the procedure, CreateTransQuery, creates an action query whose UseTransaction property is set to Yes:

The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

1. Open the sample database Northwind.mdb.



2. Create a module and type the following line in the Declarations section if it is not already there:




Option Explicit 

3. Type the following procedure:




Function CreateTransQuery()

   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim prpUseTrans As Property
   Dim strSQLString As String

   strSQLString = "UPDATE Categories SET Categories.CategoryName"
   strSQLString = strSQLString & " = 'Drinks' WHERE"
   strSQLString = strSQLString & " Categories.CategoryID = 1;"
   Set db = CurrentDb
   Set qd = db.CreateQueryDef("qryUseTransTest", strSQLString)
   Set prpUseTrans = qd.CreateProperty("UseTransaction", dbBoolean, True)
   qd.Properties.Append prpUseTrans

End Function 

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.



2. Open the Customers table and view the first record. Note that it contains the following data:




      CustomerID:    CompanyName:
      -----------    --------------------
      ALFKI          Alfred's Futterkiste 

3. Close the table. Then, create a new update query based on the Customers table, and add the following fields:




      Query: qryUpdateCustomers
      -------------------------
      Type: Update Query

      Field: CustomerID
         Criteria: "ALFKI"
      Field: CompanyName
         Update To: "Alfred's Co." 

4. Click anywhere in the upper portion of the QBE grid. Then, on the View menu, click Properties to view the query's property box.



5. Set the UseTransaction property to No.



6. On the Query menu, click Run. Note that you receive the message:




   You are about to update 1 row(s).

   Once you click Yes, you can't use the Undo command to reverse the
   changes. Are you sure you want to update these records? 

7. Click No, indicating that you do not want to commit the changes.



8. Close the query and save it as qryUpdateCustomers.



9. Open the Customers table, and view the first record. Note that now the data in the CompanyName field contains "Alfred's Co."

REFERENCES

For more information about the UseTransaction property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "UseTransaction property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: prb

Keywords : kbusage GnlMu QryUpdat QryDel QryAppnd QryMktbl
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb