Microsoft KB Archive/322676

= ACC2002: Update Query Still Modifies Records After You Cancel the Query =

Article ID: 322676

Article Last Modified on 4/7/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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

ALFKI Alfred's Futterkiste

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

Query: Query1 -- Type: Select Query

Field: CustomerID Criteria: &quot;ALFKI&quot;

Field: CompanyName

 Save the query as Query1, and then on the Query menu, click Update Query.</li> Change the query to the following:

<pre class="fixed_text">Query: Query1 -- Type: Update Query

Field: CustomerID Criteria: &quot;ALFKI&quot;

Field: CompanyName Update To: &quot;Alfred's Co.&quot;

</li> Right-click anywhere in the top portion of the design grid, and then click Properties. Notice that the UseTransaction property is set to No.</li> 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?

</li> Click No, to indicate that you do not want to commit the changes.</li> Open the Customers table. Notice that the data in the CompanyName field in the first record has been changed to &quot;Alfred's Co.&quot;</li></ol>

<div class="references_section">