How To Perform Transaction Based Processing using ADO in Access

MS Access is able for doing transaction-based processing and passes them to any of the backend DBMS system. As the result, transaction is managed by the DBMS rather than by MS Access. SQL commands are passed to DBMS and executed by using ADO Connection object. Here is an example shown to explain the complete criteria.

We have created a table with the fields, Structure of the table is shown in Fig: - 1.1.

Transaction Based Processing In MS Access Using ADOs Fig-1.1

Fig-1.1

Create a form with a button for which you will implement the Code. As shown in Fig: - 1.2.

Transaction Based Processing In MS Access Using ADOs Fig-1.2

Fig-1.2

When you will click on to the button then connection will get established and transaction will start and after successful transaction a message box will appear, as shown in Fig: - 1.3.

Transaction Based Processing In MS Access Using ADOs Fig-1.3

Fig-1.3

After the successful transaction, a new record will get stored into the table, shown in Fig: - 1.4.

Transaction Based Processing In MS Access Using ADOs Fig-1.4

Fig-1.4

If the data already exist into the table and you will click on to the transaction button then a message box will open telling about the failure of transaction. As shown below in Fig: - 1.5.

Transaction Based Processing In MS Access Using ADOs Fig-1.5

Fig-1.5

Here is the complete VBA code given for the above procedure :-

Note:-Please do add the reference of Microsoft ActiveX Data Objects 2.x Library.

Function AddDataToAccess() As Boolean
On Error GoTo Catch

Dim strRecord As String
Dim intAffectedRecord As Integer
Dim objCnn As ADODB.Connection

Set objCnn = CurrentProject.Connection
strstrRecord = "insert into table1(Serial_No, Tranc_Data) values (1, 'Transaction_1')"

objCnn.BeginTrans
objCnn.Execute strstrRecord, intAffectedRecord

If intAffectedRecord <> 1 Then
objCnn.RollbackTrans
AddDataToAccess = False
Exit Function
End If
objCnn.CommitTrans
AddDataToAccess = True

objCnn.Close
Set objCnn = Nothing

Exit Function

Catch:
AddDataToAccess = False
End Function

‘---------------Code for On Click event of the “Transaction” button---------------

Private Sub cmdTransaction_Click()
If AddDataToAccess Then
MSgBox "Transaction successfully made", vbInformation, "Success"
Else
MSgBox "Transaction Failed", vbInformation, "Failure"
End If
End Sub


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.