|
Custom Search
| |
|
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:
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
Table name: Country
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...
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.
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.
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
Happy Coding!
|
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |