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.
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.
Article written with contributions from Ben Clothier