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.

---------------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. ERP Makers 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.


 

BUY SERVICES CONTACT LOCAL MANAGERS