Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
ACC2000: Action Query Commits Changes to Data When Cancelled
Article ID: 208184 - View products that this article applies to.
This article was previously published under Q208184
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.
Set the UseTransaction property to Yes before running the action query.
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:
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
Steps to Reproduce Behavior
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.