How to do database transaction processing by using MS Access

How to do transaction based processing in MS Access

MS Access is an all-in-one and powerful development environment for office-based applications. It 's able to do transactional operations in an "all-or-nothing" fashion, just like any other mainstream DBMS systems out there such as Oracle, MySQL, SQL Server, etc. Either all of the data modifications are carried out in the database, or nothing is carried out.

A simple ACID (atomic, consistent, isolated and durable) database transaction does the following four steps:

Begin the transaction.
Execute a set of data manipulations (via SQL queries such as insert, update, or delete).
If no errors occur in any part of step 2, then commit the transaction.
If errors occur in any part of step 2, then rollback the transaction.

There are two aspects of transactional support in Access.

First, the transaction can happen inside Access JET Engine. That is, operations like INSERT, UPDATE, and DELETE run inside the same Access database (the same .mdb or .accdb file), this is because Access JET engine inherently supports transaction based processing. See demo 1 below for example.

Second, Access is able to pass transaction-based operations to a backend DBMS system such as Oracle. In this case, transaction is managed by the DBMS rather than by Access. The passing of SQL commands to the DBMS is through an ODBC or ODBC-less connection and executed by using ADO Connection object. See demo 2 below for example.

Here is the structure for the two tables used in the demo code on this page.

Table name: Contact

Column Name	Data Type	Other Attributes
ID	Number	Primary Key
Contact_Name	String	Not Null

Table name: Country

Column Name	Data Type	Other Attributes
ID	Number	Primary Key
Country_Name	String	Not Null

To get the code on this page to work, you need to add a reference to Microsoft ActiveX Data Objects Library (any version from 2.0 to 2.8) in Northwind database.

In VBA editor, click Tools -> References...

Microsoft ActiveX Data Objects Library reference

Demo 1: Transaction processing inside Access database

This function shows how to do transaction processing in an Access database. Data is inserted into two tables - Contact and Country. If the first INSERT succeeds, it will continue to do the second INSERT. Otherwise, it rolls back the transaction so nothing is inserted.

If you are not familiar with ADO's Execute method, here is Microsoft Reference page about Execute Method (ADO Connection).

Function AddDataToAccess() As Integer
    On Error Goto Catch
    
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim intRecAffected1 As Integer
    Dim intRecAffected2 As Integer
    Dim objCnn As ADODB.Connection
    
    '' Establish connection to the Access database's JET engine.
    Set objCnn = CurrentProject.Connection
                
    '' The first INSERT query
    strSQL1 = "insert into Contact(ID, Contact_Name) values (1, 'John')"

    '' The second INSERT query
    strSQL2 = "insert into Country(ID, Country_Name) values(1, 'France')"
        
    '' Transaction is started.
    objCnn.BeginTrans
    
    '' Execute the first INSERT query
    objCnn.Execute strSQL1, intRecAffected1

    '' Test result
    If intRecAffected1 <> 1 Then '' Adding data to Contact table failed
        objCnn.RollbackTrans '' Roll back transaction
        AddDataToAccess = 1 '' Indicates the first insert failed
        Exit Function '' Exit the function immediately
    End If
    
    '' Now we can continue to execute the second INSERT query
    objCnn.Execute strSQL2, intRecAffected2
    
    '' Test result
    If intRecAffected2 <> 1 Then '' Adding data to Country table failed
        objCnn.RollbackTrans '' Roll back transaction
        AddDataToAccess = 2 '' Indicates the second insert failed
        Exit Function '' Exit the function immediately
    End If
    
    '' So far so good, we commit the transaction.
    objCnn.CommitTrans
    AddDataToAccess = -1 '' Successful
    
    '' Close the connection and clean up
    objCnn.Close
    Set objCnn = Nothing
        
    Exit Function
        
Catch:
    AddDataToAccess = 3 '' Some other error occurred
    MsgBox "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

For testing, add the code below to the first button's subroutine.

Test database transaction in Access

Private Sub cmdAddToLocal_Click()
    Dim intReturn As Integer
    
    intReturn = AddDataToAccess()
    
    If intReturn = -1 Then
        MsgBox "Adding data to local Access table Contact and Country data was successful."
    Else
        MsgBox "Failed to add data to local Access table Contact and Country. Nothing was added."
    End If
End Sub

Demo 2: Hand over transaction processing to Oracle

This function shows how to organize transactions in an Access database but pass the command to Oracle for processing. The code is nearly identical to the first demo and the only difference is that the connection is established to an Oracle database rather than the local Access database.

To make this demo work, you need to create two tables in your Oracle database. The Oracle table has the same column names and data types as the Access tables.

CREATE TABLE CONTACT
(
    ID INTEGER PRIMARY KEY NOT NULL,
    CONTACT_NAME VARCHAR2(100 BYTE) NOT NULL
);

CREATE TABLE COUNTRY
(
    ID INTEGER PRIMARY KEY NOT NULL,
    COUNTRY_NAME VARCHAR2(100 BYTE) NOT NULL
);

Note that, when connecting to another DBMS, we can use either ODBC based connection or DSN-Less connection.

For how to set up and use ODBC Data Source Administrator, refer to the following articles.

Using the ODBC Data Source Administrator

Step-by-step guide on how to set up DSN in ODBC Data Source

For this demo, the ODBC driver we used is Microsoft ODBC driver for Oracle.

There are two ODBC connection string functions - GetODBCCnnString() and GetDSNLessCnnString() - used in the code below. They are listed at the end of this article.

Function AddDataToOracle() As Integer
    
    On Error Goto Catch
    
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim intRecAffected1 As Integer
    Dim intRecAffected2 As Integer
    Dim objCnn As ADODB.Connection

    '' Create the connection object.
    Set objCnn = New ADODB.Connection

    '' Get the ODBC based connection string.
    objCnn.ConnectionString = GetODBCCnnString()
    
    '' We can also use this ODBC-less connection string
    ''objCnn.ConnectionString = GetODBCLessCnnString()
    
    '' Open connection to Oracle
    objCnn.Open

    '' The first INSERT query
    strSQL1 = "insert into Contact(ID, Contact_Name) values (1, 'John')"
                
    '' The second INSERT query
    strSQL2 = "insert into Country(ID, Country_Name) values(1, 'France')"
        
    '' Transaction is started.
    objCnn.BeginTrans
    
    '' Execute the first INSERT query
    objCnn.Execute strSQL1, intRecAffected1
    
    '' Test result
    If intRecAffected1 <> 1 Then '' Adding data to Contact table failed
        objCnn.RollbackTrans '' Roll back the transaction
        AddDataToOracle = 1 '' Indicates the first insert failed
        Exit Function'' Exit the function immediately
    End If
    
    '' We can now continue to execute the second INSERT query
    objCnn.Execute strSQL2, intRecAffected2
    
    '' Test result
    If intRecAffected2 <> 1 Then '' Adding data to Country table failed
        objCnn.RollbackTrans '' Roll back the transaction
        AddDataToOracle = 2 '' Indicates the second insert failed
        Exit Function'' Exit the function immediately
    End If
    
    '' So far so good, we commit the transaction.
    objCnn.CommitTrans
    AddDataToOracle = -1 '' Successful
    
    '' Close the connection and clean up
    objCnn.Close
    Set objCnn = Nothing
        
    Exit Function
        
Catch:
    AddDataToOracle = 3 '' Some other error occurred
    MsgBox "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

For testing, add the code below to another button's subroutine.

Test database transaction in Access for Oracle

Private Sub cmdAddDataToOracle_Click()
    Dim intReturn As Integer
    
    intReturn = AddDataToOracle()
    
    If intReturn = -1 Then
        MsgBox "Adding data to Oracle table Contact and Country was successful."
    Else
        MsgBox "Failed to add data to Oracle table Contact and Country. Nothing was added."
    End If
End Sub

ODBC connection string functions

Below are the two functions GetODBCCnnString() and GetDSNLessCnnString() that are used in the demo code above. The two functions return two ADO connection strings. The first one requires a DSN to be set up in ODBC Administrator. The second function does not require a DSN.

Please note that, using the two functions to connect to the Oracle database (or any other database management systems) is similar to using Pass-Through queries that you can build in Access. Instead of putting the ODBC DSN (Data Source Name) in the Query Properties window (Property Sheet) for a Pass-Through query, we coded the connection credentials in the VBA function. The underlying logic is the same - it sends uninterpreted SQL statements (or stored procedure names, function names) to a database management system for execution. Access does not perform syntax checks for the SQL passed through.

(1) Functions GetODBCCnnString() - This function requires a DSN to be set up in ODBC Administrator.

'' This connection requires a DSN to be set up in ODBC Data Source Administrator.
Function GetODBCCnnString() As String
    On Error Goto Catch
    
    Dim strDSN As String
    Dim strUID As String
    Dim strPWD As String
    Dim strCnn As String
    
    strDSN = "TestDB"
    strUID = "geeksengine"
    strPWD = "some_password_here"
    
    '' Connection string for ADO Connection object
    strCnn = "DSN=" & Trim(strDSN) & ";UID=" & Trim(strUID) & ";PWD=" & Trim(strPWD)
    GetODBCCnnString = strCnn
    
    Exit Function
    
Catch:
    GetODBCCnnString = ""
    MsgBox "GetODBCCnnString function" & vbCrLf & vbCrLf & "Error#: " _
           & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

(2) Function GetDSNLessCnnString() - requires no DSN

Here we need to put the database server name, schema/user name, and password in the connection string. Driver is Microsoft ODBC For Oracle.

'' This connection method does not need DSN to be set
'' up in ODBC Data Source Administrator.
Function GetDSNLessCnnString() As String
    On Error Goto Catch
    
    Dim strSrv As String
    Dim strUID As String
    Dim strPWD As String
    Dim strCnn As String
    
    strSrv = "TestDB"
    strUID = "geeksengine"
    strPWD = "some_password_here"
    
    '' Connection string for ADO Connection object
    strCnn = "Driver={Microsoft ODBC For Oracle};" & _
           "Server=" & strSrv & ";" & _
           "Uid=" & strUID & ";" & _
           "Pwd=" & strPWD & ";"

    GetDSNLessCnnString = strCnn
    
    Exit Function
    
Catch:
    GetDSNLessCnnString = ""
    MsgBox "GetDSNLessCnnString function" & vbCrLf & vbCrLf & "Error#: " _
           & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

Other Recent Articles from the MS Access category:

1.	Examples of MS Access DateDiff function used in query and VBA code
2.	MS Access DateDiff function
3.	How to find out your computer name and username by VBA
4.	Examples of MS Access DatePart function
5.	MS Access DatePart function
6.	Examples of MS Access DateAdd function
7.	MS Access DateAdd function
8.	IIF function basics - the CASE statement of MS Access
9.	MS Access Date Expression
10.	Solved: MS Access error "The text is too long to be edited"
11.	Create MS Access Combo Box essential properties by VBA code
12.	Create MS Access Combo Box essential properties manually
13.	How to do text search in MS Access programmatically
14.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
15.	How to easily get a list of field names in MS Access
16.	How to count distinct records in MS Access
17.	How to open a document (local/network file or web page) from MS Access
18.	How to use ADOX to create unique composite index - the VBA approach
19.	How to do cross-table update queries in MS Access - the right way
20.	Three efficient ways to get the number of records by using VBA
21.	How to create a composite unique index (not as a primary key) in MS Access
22.	Use VBA to get the correct number of records in a Recordset object
23.	Disable Access Prompt when a record is changed, table deleted, or action queries run
24.	How to hide and unhide a MS Access object
25.	How to return multiple values from a VBA function (Part 3)
26.	How to return multiple values from a VBA function (Part 2)
27.	How to return multiple values from a VBA function (Part 1)
28.	Three ways to programmatically duplicate a table in MS Access by VBA
29.	Create a DLL by CSharp or VB.Net for VBA
30.	How to correctly reference and call a DLL
31.	How to register a C# or VB.Net DLL
32.	Email address validation by Regular Expressions using VBA
33.	Fix MS Access error: Query must have at least one destination field
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search