ACC2002: Update Query Still Modifies Records After You Cancel the Query (322676)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q322676
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 815033.

SYMPTOMS

When you initiate an Update query and then try to cancel it, the records in the underlying table may still be modified.

CAUSE

This issue may occur if you try to run an Update query that was converted from a Select query.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft Office XP Service Pack 2.

RESOLUTION

To resolve this problem, obtain the latest service pack for Office XP. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

307841 OFFXP: How to Obtain the Latest Office XP Service Pack

Note After you apply the service pack, you must still modify the UseTransaction property in your existing Update query. To do this, follow these steps:
  1. Open the query in Design view.
  2. On the View menu, click Properties.
  3. In the Query Properties dialog box, click Use Transaction, click the down arrow at the right side of the Use Transaction field, and then click Yes.
  4. Close the Query Properties dialog box, click Save on the File menu, and then close the query design grid.

MORE INFORMATION

When you convert a Select query to an action query, the UseTransaction property is set to No by default. This setting commits the changes to the original data as the query runs. Because the query is run as soon as you click Run on the Query menu (or double-click the query name in the database window), the Microsoft Jet database engine has no way of rolling back the changes.

When the property is set to Yes, Jet stores the modified records in a cache and commits them to the original data only when the query is completed. If you decide to cancel the query, the cached changes are never applied to the data.

After you apply Office XP Service Pack 2, the default value of the UseTransaction property in action queries converted from Select queries is Yes.

Steps to Reproduce the Problem

  1. On a computer that has not been upgraded to Office XP Service Pack 2, start Access, and then open the Northwind.mdb sample database.
  2. Open the Customers table, and then view the first record. Note that the CustomerID field contains the following text:

    ALFKI Alfred's Futterkiste

  3. Close the Customers table, and then create the following Select query based on the Customers table: 

    Query: Query1 
------------------
Type: Select Query


Field: CustomerID 
Criteria: "ALFKI" 

Field: CompanyName

  4. Save the query as Query1, and then on the Query menu, click Update Query.
  5. Change the query to the following: 

    Query: Query1 
------------------ 
Type: Update Query 

Field: CustomerID 
Criteria: "ALFKI" 

Field: CompanyName 
Update To: "Alfred's Co."

  6. Right-click anywhere in the top portion of the design grid, and then click Properties. Notice that the UseTransaction property is set to No.
  7. On the Query menu, click Run. Note that you receive the following 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?
  8. Click No, to indicate that you do not want to commit the changes.
  9. Open the Customers table. Notice that the data in the CompanyName field in the first record has been changed to "Alfred's Co."

REFERENCES

For more information about the UseTransaction property, click Microsoft Access Help on the Help menu, type usetransaction property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:4/7/2006
Keywords:kbQFE KBHotfixServer kbbug kbfix kbOfficeXPsp2fix KB322676
©2004 Microsoft Corporation. All rights reserved.