Microsoft KB Archive/171851: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 100: | Line 100: | ||
Dim strSQLString As String | Dim strSQLString As String | ||
strSQLString = | strSQLString = "UPDATE Categories SET Categories.CategoryName" | ||
strSQLString = strSQLString & | strSQLString = strSQLString & " = 'Drinks' WHERE" | ||
strSQLString = strSQLString & | strSQLString = strSQLString & " Categories.CategoryID = 1;" | ||
Set db = CurrentDb | Set db = CurrentDb | ||
Set qd = db.CreateQueryDef( | Set qd = db.CreateQueryDef("qryUseTransTest", strSQLString) | ||
Set prpUseTrans = qd.CreateProperty( | Set prpUseTrans = qd.CreateProperty("UseTransaction", _ | ||
dbBoolean, True) | dbBoolean, True) | ||
qd.Properties.Append prpUseTrans | qd.Properties.Append prpUseTrans | ||
Line 131: | Line 131: | ||
Field: CustomerID | Field: CustomerID | ||
Criteria: | Criteria: "ALFKI" | ||
Field: CompanyName | Field: CompanyName | ||
Update To: | Update To: "Alfred's Co." | ||
</pre></li> | </pre></li> | ||
<li>Click anywhere in the upper portion of the QBE grid. Then, on the View menu, click Properties to view the query's property box.</li> | <li>Click anywhere in the upper portion of the QBE grid. Then, on the View menu, click Properties to view the query's property box.</li> | ||
Line 146: | Line 146: | ||
<li>Click No, indicating that you do not want to commit the changes.</li> | <li>Click No, indicating that you do not want to commit the changes.</li> | ||
<li>Close the query and save it as qryUpdateCustomers.</li> | <li>Close the query and save it as qryUpdateCustomers.</li> | ||
<li>Open the Customers table, and view the first record. Note that now the data in the CompanyName field contains | <li>Open the Customers table, and view the first record. Note that now the data in the CompanyName field contains "Alfred's Co."</li></ol> | ||
Line 154: | Line 154: | ||
== REFERENCES == | == REFERENCES == | ||
For more information about the UseTransaction property, search the Help Index for | For more information about the UseTransaction property, search the Help Index for "UseTransaction property." | ||
</div> | </div> |
Latest revision as of 12:29, 21 July 2020
Article ID: 171851
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q171851
Novice: Requires knowledge of the user interface on single-user computers.
SYMPTOMS
An action query always commits changes to data in a table. This occurs even if you click No when Microsoft Access prompts you to commit the changes.
CAUSE
The action query's UseTransaction property is set to No.
RESOLUTION
Set the UseTransaction property to Yes before running the action query.
STATUS
This behavior is by design.
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 actionquery whose UseTransaction property is set to Yes:
- Open the sample database Northwind.mdb.
Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit
Type the following procedure:
Function CreateTransQuery() Dim db As Database Dim qd As 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
- Open the sample database Northwind.mdb.
Open the Customers table and view the first record. Note that it contains the following data:
CustomerID: CompanyName: ----------- -------------------- ALFKI Alfred's Futterkiste
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."
- Click anywhere in the upper portion of the QBE grid. Then, on the View menu, click Properties to view the query's property box.
- Set the UseTransaction property to No.
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?
- Click No, indicating that you do not want to commit the changes.
- Close the query and save it as qryUpdateCustomers.
- 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, search the Help Index for "UseTransaction property."
Keywords: kbprb kbusage KB171851