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
.ConectionString = "some connection string to SQL Server"
If .State = adStateClosed Then
'Uh oh, can't connect to SQL
Set rs = New ADODB.Recordset
.ActiveConnection = con
.Open "tblOrders", con, adOpenDynamic, adOpenOptomistic
con.BeginTrans '<--- FUN STARTS HERE
'Add new record details here
'Now open a recordset to another table and add new a new record:
.Open "tblOrderDetails", con, , adOpenDynamic, adOpenOptomistic
'Add new record details here too
con.CommitTrans '<--- If you got this far then commit batch
Set rs = Nothing
Set con = Nothing
MsgBox Err.Description, vbInformation, "Error:"
Con.RollBackTrans '<---Something wrong? Cancel the whole thing
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
.Execute "...", dbFailOnError 'Some SQL statements
.Execute "...", dbFailOnError 'More SQL statements
'... do whatever else needs to be done within the transaction.
Set db = Nothing
Set ws = Nothing
MsgBox Err.Description, vbInformation, "ErrorL"
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