ACC2000: Update Query Still Modifies Records After You Cancel the Query (815033)


The information in this article applies to:

  • Microsoft Access 2000
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 2002 version of this article, see 322676.

SYMPTOMS

When you initiate an Update query and then try to cancel the query, 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.

RESOLUTION

A supported fix is now available from Microsoft, but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Office 2000 Service Pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.
The Global version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. 
   Date         Time   Version         Size       File name
   --------------------------------------------------------------
   19-Nov-2002  06:04  9.0.0.7114      4,681,782  Msaccess.exe 
   21-Nov-2002  01:16                  2,558,464  Msaccess_admin.msp
   21-Nov-2002  00:43                    984,576  Msaccess.msp
Note After you apply the hotfix, set the Use Transaction property to Yes in your existing Action queries that originated as Select queries. To do so, use the method described in the "Workaround" section of this article.

WORKAROUND

To work around this problem, modify the Use Transaction property in Action queries converted from Select queries. 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.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you convert a Select query to an action query, the Use Transaction 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 the hotfix or service pack described in the "Resolution" section of this article, the default value of the Use Transaction property in action queries that are converted from Select queries is Yes.

REFERENCES

For more information about the Use Transaction property, click Microsoft Access Help on the Help menu, type use transaction property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:10/7/2005
Keywords:kbOffice2000preSP4fix kbAccess200fix kbQFE kbbug kbfix KB815033
©2004 Microsoft Corporation. All rights reserved.