UseTransaction Property

Applies To

Action query, QueryDef object.

Description

You can use the UseTransaction property to specify or determine whether an action query runs as a single transaction. In some situations, setting this property to No can make queries run much faster.

Setting

The UseTransaction property uses the following settings.

Setting	Description	Visual Basic

Yes	(Default) The query runs as a single transaction.	True (–1)
No	The query doesn't run as a single transaction.	False (0)

You can set the UseTransaction property by using the query's property sheet or Visual Basic. To set this property with Visual Basic, you must first add it to a QueryDef object's Properties collection by using the CreateProperty method.

Remarks

When a make-table, delete, append, or update query has its UseTransaction property set to Yes, the Microsoft Jet database engine stores the returned records in a cache and, if necessary, stores the results in a temporary database on disk. When the query has finished, the Jet database engine reads the records from the temporary database and writes them back to the original database.

You may get substantial performance benefits by setting the UseTransaction property to No in the right circumstances. When the UseTransaction property is set to No, the user will never encounter problems associated with too many lock requests. In addition, the Jet database engine doesn't store query results in a temporary database, thereby achieving a substantial performance increase.

If there is a large number of records that must be written to the temporary database, performance may suffer as a result of running an action query as a single transaction. In addition, when running delete or update queries in a shared database, a large number of lock requests are generated. This may cause performance to suffer and in some Network Operating Systems like Netware it can cause the query to fail when the lock requests exceed 10,000.

If a transaction can't be completed, an error message appears and you have the option of saving any changes.

When the UseTransaction property is set to No, the only way to roll back an entire transaction is to use the DAO RollBack method in Visual Basic.