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:

Public Function CreateOrder(lngCompanyID as Long)
On Error GoTo ErrorHandler
Dim rs As ADODB.Recordset
Dim strSQL as String
Dim con as ADODB.Connection
Set con = New ADODB.Connection
With con
 .ConectionString = "some connection string to SQL Server"
 .Open
 If .State = adStateClosed Then
  'Uh oh, can't connect to SQL
 End If
End With
Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = con
    .Open "tblOrders", con, adOpenDynamic, adOpenOptomistic
    con.BeginTrans  '<--- FUN STARTS HERE
    .AddNew
    'Add new record details here
    .Update
    'Now open a recordset to another table and add new a new record:
    .Open "tblOrderDetails", con,  , adOpenDynamic, adOpenOptomistic
    .AddNew
    'Add new record details here too
    .Update
End With

ExitProcedure:
    con.CommitTrans  '<--- If you got this far then commit batch
    Set rs = Nothing
    Set con = Nothing
    ExitFunction
ErrorHandler:
    MsgBox Err.Description, vbInformation, "Error:"
    Con.RollBackTrans  '<---Something wrong? Cancel the whole thing
    Resume ExitProcedure
End Function

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:

Public Function CreateOrder(lngCompanyID As Long)
On Error GoTo ErrorHandler
Dim ws As DAO.Workspace
Dim db As DAO.Database

Set ws = DBEngine.Workspaces(0) 'Use default workspace
Set db = ws.Databases(0) 'Use default database
With db    
    ws.BeginTrans
    .Execute "...", dbFailOnError 'Some SQL statements
    .Execute "...", dbFailOnError 'More SQL statements
    '... do whatever else needs to be done within the transaction.
    ws.CommitTrans
End With
ExitProcedure:
    Set db = Nothing
    Set ws = Nothing
    Exit Function
ErrorHandler:
    MsgBox Err.Description, vbInformation, "ErrorL"
    ws.Rollback
    Resume ExitProcedure
End Function

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 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.

Posted in SQL Server, VBA
7 comments on “Use Batch Transactions with SQL Server to Guarantee Results
  1. Chris Gilbert says:

    you must mean adLockOptimistic.

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

  2. Yahia says:

    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
    Yahia
    elmiftahyahia@gmail.com

    • Juan Soto says:

      Hi Yahia,

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

      Regards
      Juan

  3. Jurgen says:

    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 says:

      Jurgen,

      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 says:

        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 says:

          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.

          Juan

Free email subscription

Enter your email address:


Facebook

Twitter

Blog Archives

ITImpact
%d bloggers like this: