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 | Visual Basic | Description |
|---|---|---|
| Yes | True (–1) | (Default) The query runs as a single transaction. |
| No | False (0) | The query doesn't run as a single transaction. |
You can set the UseTransaction property by using the query's property sheet or Visual Basic. To set this property in a Microsoft Access database (.mdb) 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 ADO RollbackTrans or DAO Rollback method in Visual Basic.