Transactions and how to use them in Microsoft Access

by Philipp Stiefel, originally published December 22nd, 2015
last updated December 1st, 2017

Transactions are a fundamental concept of relational databases. Transactions group multiple operations in a database together to one unit of work. This unit of work can either succeed or fail as a whole.  If it succeeds, all the changes within the transaction are committed and made durable. If any part of the transaction fails and the changes are rolled back, all the data is as it was before the transaction.

One classic example for a transaction is the transfer of money between bank accounts. It must absolutely never happen that money is taken out of one account without the same amount being put into the other account. To ensure this behavior, the update of both accounts needs to be grouped into a transaction.

Volumes have been written on transactions and the ACID paradigm, so I am not diving any deeper into general database theory here.

Access, or rather the Jet/ACE-Database-Engine, support transactions as well. And it uses them implicitly by default for each action query that is executed. Only a single query is executed within the transaction then, so you hardly notice a transaction being involved. Still, it ensures that all the changes to the data by the query can be rolled back in case of an error. (You can disable using a transaction for a query by setting the Use Transaction-Property to “No”.)

You can use explicit transactions in your VBA code as well. By doing this you can group several different queries in a single transaction and then either commit all of them or roll back all the changes. As you control the transaction with your code, you can do this independently of the success or failure of the queries.

Ok, now let’s have a look at the implementation of an explicit transaction in VBA. (You cannot use transactions with macros in Access.)

The implementation is fairly simple. Before you execute the first action query, you instruct the DBEngine to start a transaction using the BeginTrans-Method. Then you execute all the action queries that you want to group into the transaction.

If there was no error, you will usually commit the transaction calling the CommitTrans-Method and by that persist all the changes of all the queries. But that is up to you. You can roll back a transaction with the Rollback-Method even if all of the queries succeeded without error. You can still commit a transaction if some of the queries within it failed.

Ok, now let’s look at the code to implement a transaction.

That's not too difficult, isn't it? But there are two important things you need to be aware of!

It is very important to handle errors that might occur during the execution of the queries. In the case of an error, you need to roll back the transaction. Otherwise, the database pages (an internal storage unit of several records) that were affected by the transaction will remain locked and no other user can change those records. Thus leaving a part of your database in read-only state for everyone else until the instance of Access that was starting the transaction is closed. – Which will roll back the transaction.

The other important thing here is using the dbFailOnError-Option for the CurrentDb.Execute-Method. If you omit this option, the action SQL statement can silently fail. E.g. if the query causes a key- or validation-rule-violation, the records will not be inserted, updated or delete, but there will be no error in VBA. You still could commit the transaction then, but only the changes of the other SQL statements were really executed. – That is exactly the problem we usually want to prevent by using transactions.

You can group any number of queries into a transaction. But always keep in mind: The affected records (database pages) will be locked until the transaction completes. An action query or manual edit of another user accessing these records will fail as long as the locks on the records by from within the transaction are not released.

If you have several different operations using transactions to modify the same tables, always access these tables in the same order otherwise a deadlock (two transactions waiting for each other to complete and release the locks) may occur.

Finally an advice about the conceptual use of transactions. While technically possible, I strongly advise against including any interaction with the user ("Do you want to save the changes? - Yes/No") in a transaction.

As stated above, an open transaction will lock parts of your database until it is completed by a commit or rollback. It is a bad idea to wait for user input to complete the transaction. The user might be occupied otherwise and leave the transaction open for a while, or he might go to his lunch break or off to his three-week vacation. - And part of the database is locked against other write operations until he returns.

There is another reason I advise against including user interaction in a transaction. This one is specific to Access and will not apply to transactions with client/server databases like Microsoft SQL Server.

If there is a technical problem disconnection the Access frontend application from the backend file during the transaction, like a network failure or a power outage, there is a risk of your database becoming corrupted. This risk of corruption exists as well should a similar incident happen during the execution of a query without a transaction. Any user interaction will inevitably increase the time until the transaction is completed thus increasing the risk of database corruption.

So please see transactions as a technical unit of work, not involving user interaction. If the operations you want to execute within the transaction require any user input, get it before and make sure it is valid and complete before you start the transaction.

Now, after I made my reservations about user interaction in transactions quite clear, I will nevertheless explain how you can use transactions with user input in forms.

With Access forms using the default data binding to a table or query, you cannot use transactions at all. The data in an Access form with that default binding is modified outside the scope of programmatically accessible workspaces. So, even if you start a transactions before modifying data in a form, any commit or rollback performed in code has no effect whatsoever

However, there is a way to make it work.

From Access 2002 onward, you can not only bind a form by setting the Recordsource of the form to a table or query name or any SQL statement, but you can as well data-bind the form by settings its Recordset property to a Recordset you created in VBA code. If you do this, the changes to the data are inside the scope of the transaction. Hence, you are now able to start a transaction, let the user edit the data in a form and then either commit or rollback the transaction.

This even works with a form containing a subform in a master-detail relationship. You then need to bind the master form and the detail subform to a Recordset.

There is a minor caveat, however. For reasons I do not understand, this does not work if you use the LinkMasterFields/LinkChildFields properties to automatically link the data in the two forms. You need to synchronize the detail form with the master for in your own code instead. This includes to set value of the foreign key field of the child form to the corresponding value of the master form. This can be done by setting the DefaultValue of a hidden control bound to the foreign key column to the key value of the master form.

Here is the screenshot of a simple master-detail-form and the corresponding VBA code to control the transaction.

The transaction management is completely manual here for the purpose of illustrating the concept. This code is by no means suitable for production use. In a production scenario, you would need to start the transaction automatically and handle all possible user interactions like closing the form, browsing through records, as well as deleting and adding new records. Implementing this in a robust way will be a challenge.

Unfortunately, the DAO data access library does not supply any property or method to get the current transactions state. There will be an error if you try to commit or rollback a non-existing transaction, but nothing prevents you from accidentally opening several nested transactions at the same time. So, you need to take utmost care when designing your transaction handling code to prevent any hard-to-detect errors creeping in there.

As I do not recommend including user interactions in transactions at all, I have never used the above approach in a production application. I cannot say if it will work reliably with multiple users or over prolonged time.

Some people do not like gaps in their auto number sequences. If you are one of them, you might get the idea to use transactions to prevent the “loss” of values from an auto number sequence by wrapping the code that inserts new records in a table with an AutoNumber.

Good idea, but it does not work. You will not get back any AutoNumber value that was used inside a rolled back transaction. These values are still “lost”.

The logic managing the auto number sequence is running outside the scope of any transaction. It has to be this way. Otherwise, any transaction that requested a new AutoNumber would block all other users from inserting new records in the same table until that transaction is completed.

You may have read other texts about transactions in Microsoft Access. They probably were dealing with DAO Workspaces for transaction management at least in the sample code. I haven’t mentioned them at all until now and even now I am not going to explain them further. Why is that so?

With Microsoft Access you will only very, very rarely use more than a single workspace. Unless you really use multiple workspaces, you are just fine with ignoring them altogether and just use the DBEngine-Object for transaction management, as I did in the sample code above.

Share this article: