Go to Top
  • No products in the cart.

Use Batch Transactions with SQL Server to Guarantee Results

Batch transactions are a great way to avoid orphan records and to guarantee the database has performed all of the actions you have requested. This post will discuss how you can incorporate batch transactions in Access VBA using SQL Server.

Why Batch?
Normally you would use referential integrity and delete cascade to avoid orphan records, for example, if you delete a customer record all of the corresponding orders, order details, contacts, etc. get deleted too. The alternative would be to use batched transactions.

Another common scenario are two or more transactions that must occur together or you end up with mixed results. For example, if you wish to create an order and order detail records at the same time. You can’t have order details without a order record, so it’s critical the order record get’s created before your order details record.

Why would the record not get created, isn’t SQL Server robust?
SQL Server ‘s transaction capability is not the issue here, your program and users are. If you have a user with a read-write lock on the entire Orders detail table, any new records may not post till that user’s lock is cleared. Given that the user may have left for lunch and leave the table in that state, your program may error out before the Reuben on Rye is consumed. There are strategies you should follow so that your program doesn’t fall to this scenario, but that’s a topic for another, (great), post.

Consider using Stored Procedures First
There, I said it. Consider using SPs first before using the technique in this article. The advantages are legion and SQL will always do a better job than Access. Still need to use Access? Then read on.

Recipe Ingredients #1: Mix ADODB connection object with recordset and error handling
To batch your SQL transactions you need to use the BeginTrans , CommitTrans and RollbackTrans actions of the ADODB connection object. Here’s an example:

You will notice that if any error does occur, it will trigger the RollBackTrans method of the connection object, which would roll back any activity since the batch started, which in this case would be either the new tblOrders record, tblOrderDetails record or both.

Recipe Ingredients #2: Using DAO Workspace and Execute method.

You may not realize it but DAO also supports transactions just as well as ADO and thus is a good choice when we’re using only Access data sources. But this time, we’ll execute a SQL statement instead of opening a recordset:

Though both DAO and ADO differs slightly in how you manage transactions, you can certainly choose to open a recordset or just execute a SQL command with either technology.

Why can’t we do this with bound forms?

Behind the scenes, Access is actually managing the transactions and will be sending transaction control messages to the backend based on the user’s interactions. At the time of writing, there is no means to override this behavior. Therefore, if you have a need for batch transaction, it may be desirable to either consider using an unbound form or a form bound to either a SQL Server View with an INSTEAD OF trigger or a stored procedure that conforms to updatability rules and binding the form with an ADO recordset, or a disconnected ADO recordset to simulate batching.

With this in mind, Leigh Purvis, an Access MVP has a testbed sample of using transactions with forms that may be interesting toward providing additional insights in how Access manages the transactions behind the curtains. You can find his example at his website and downloading the “Transaction in Forms” sample.

Happy Batching!

Article written with contributions from Ben Clothier

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

8 Responses to "Use Batch Transactions with SQL Server to Guarantee Results"

  • PaulE
    February 27, 2015 - 7:05 am Reply

    In “Recipe Ingredients #1” con.CommitTrans is on the wrong side of ExitProcedure:
    This is a recipe for an infinite looping error.

  • Chris Gilbert
    October 25, 2013 - 2:36 am Reply

    you must mean adLockOptimistic.

    also, is it necessary to set the .ActiveConnection = con when you are using.Open , con, …. ?

  • Yahia
    April 25, 2012 - 4:21 am Reply

    Dear Sir
    I need your help to perform a batch query on an Access DB . I have a multiple query with a parameter to be passed from a external text file , and I want to send the result to a text file .
    Is it possible with MS Access .
    Thank You

    • Juan Soto
      April 26, 2012 - 7:35 pm Reply

      Hi Yahia,

      You will need to use a filestream object to read the external file and assign the parameters to the query in code.


  • Jurgen
    April 18, 2012 - 1:29 pm Reply

    My current database is all in DAO code with transactions using an access 2010 database. Is it a good idea to connect to SQL-server this way (via linked tables ODBC/DSN-less) or do i need to update the code to ADO? It’s not a small interface : 160 screens with 60 users. Thx

    • Juan Soto
      April 19, 2012 - 12:03 am Reply


      You can leave it as is if you’re happy with the performance. If however you wish to speed up your app it may be done by using ADODB to issue commands directly to the server, for example, update queries run much faster on the server then using Access linked tables.

      Hope that helps.

      • Jurgen
        May 15, 2012 - 6:40 pm Reply

        When opening a recordset to add a new record there is a slowdown when i use the syntax “tablename” when i use “select * from tablename WHERE id=0” there is no slowdown. Only when the table has more than 1 milj. records and both DAO or ADO. Is this normal and should we use the 2nd syntax or?

        • Juan Soto
          May 15, 2012 - 7:18 pm Reply

          Yes, using where 1=0 is fast, and that’s why we use it when a form loads. You say you’re adding a record, are you doing that with code or via a form? If through code you should use ADODB instead of DAO, it’s usually a little faster.


Leave a Reply

Your email address will not be published. Required fields are marked *