Session Transaction in Microsoft Access Database - VBA

Welcome to Logically Proven Blog.

This post teaches you "how to implement session transaction while working with data modifications in Microsoft Access database using VBA".

It is a good practice to implement session transaction while you are working with database modification like inserting, updating and deleting.

Using DAO or ADO connection objects, we can perform the database operations in MS Access.

The reason behind to implement session transaction is while you are trying to do some modifications to the database, unfortunately if the operation got failed in middle which leads to major consequences. To avoid these kind of consequences, we can resolve by rolling back to the previous state to achieve database consistency through out the application.

To know more about the database consistency, please understand the ACID properties in database. Please follow the link -  http://en.wikipedia.org/wiki/ACID

Now we see how to implement session transaction in case of DAO and ADO connection objects.

Using DAO Connection Object:

Please follow the example to understand easily.

Sub DAO_transaction()
 
 Dim strSQL As String
 Dim db As DAO.Database
 Dim wrk As Workspace
 
 On Error GoTo TrapError
 
 Set db = CurrentDb
 Set wrk = DBEngine.Workspaces(0)
 
 wrk.BeginTrans
 strSQL = "Update sysInfo Set InvoiceOR=False"
 db.Execute strSQL, dbFailOnError
 wrk.CommitTrans
 
 Exit_Sub :
 Set db = Nothing
 Set wrk = Nothing
 Exit Sub
 
 TrapError :
 
 MsgBox "Failed: " & Err.Description
 wrk.Rollback
 Err.Clear
 Resume Exit_Sub
 
End Sub

In the above example to roll back the transaction we should include On Error GoTo TrapError statement for error handling.

To achieve roll back, we are using DAO workspace object. Queries which modifies data in the database are included between BeginTrans and CommitTrans. When the queries get executed, the data changes will not replicate immediately in the database. The changes replicates only when workspace.CommitTrans is executed. 

If any error occurs inside workspace then the TrapError is called and workspace.Rollback statement executes which Undo all the changes which we did inside workspace.

In the above example we used direct query. Suppose if we want to execute saved queries and to achieve session transaction we have to make use queryDef. 

Please see the below example.

Public Function fnImportSendReceipts()
 
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim ws As DAO.Workspace
 Dim qdf As DAO.QueryDef
 
 On Error GoTo err_handle
 Set ws = DBEngine.Workspaces(0)
 Set db = CurrentDb
 
 ws.BeginTrans
 
 Set qdf = db.QueryDefs("Qry_UpDateLock")
 qdf.Execute dbFailOnError
 ws.CommitTrans
 
 mem_clean :
  Set qdf = Nothing
  Set rs = Nothing
  Set ws = Nothing
  Set db = Nothing
  Exit Function
 
 err_handle :
  ws.Rollback
  DoCmd.OpenQuery "Qry_ReleaseLock"
  GoTo mem_clean
 
End Function

Using ADO Connection Object:

If  you understand the first scenario, then it is easy to understand this example. We are using different connection objects to connect to the database but the concepts are same.

Sub ADO_Transaction()
 
 Dim cmd As ADODB.Command
 Dim cn As ADODB.Connection
 
 Set cmd = CreateObject("ADODB.Command")
 Set cn = CurrentProject.Connection
 
 cmd.CommandText = "Update sysInfo Set InvoiceOR=False"
 cmd.ActiveConnection = cn
 cmd.ActiveConnection.BeginTrans
 cmd.Execute, , adExecuteNoRecords
 
 If Err <> 0 Then
  cmd.ActiveConnection.RollbackTrans
  Else
   cmd.ActiveConnection.CommitTrans
 End If
 
End Sub

Then it is time to know some exceptions with respect to session transactions.

DoCmd Statement in Transaction Not Affected by Rollback

Why?

Transaction processing in Microsoft Access is valid only on virtual table (VT) objects, such as dynasets. The DoCmd Object starts another Microsoft Jet database engine session to process its arguments.

Because the Jet database engine manages transaction processing, a new session of the Jet database engine contains its own transaction management. The two are mutually exclusive; transactions in one are not managed or affected by transactions in the other.

The simplest workaround for this problem is the QueryDef.Execute method.

Note: The QueryDef.Execute method works only when a QueryDef action is based on an action query (update, insert, delete) that does not return a recordset.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven,

Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.