The first one requires a DSN to be set up in ODBC Administrator. The two functions return two ADO connection strings.
#Setting up microsoft access database code
Nothing was added."īelow are the two functions GetODBCCnnString() and GetDSNLessCnnString() that are used in the demo code above. MsgBox "Failed to add data to Oracle table Contact and Country. MsgBox "Adding data to Oracle table Contact and Country was successful." ObjCnn.RollbackTrans '' Roll back the transactionĪddDataToOracle = 1 '' Indicates the first insert failed Exit Function '' Exit the function immediately End If '' We can now continue to execute the second INSERT queryĪddDataToOracle = 2 '' Indicates the second insert failed Exit Function '' Exit the function immediately End If '' So far so good, we commit the transaction.ĪddDataToOracle = -1 '' Successful '' Close the connection and clean upĪddDataToOracle = 3 '' Some other error occurredįor testing, add the code below to another button's subroutine. '' We can also use this ODBC-less connection string ''objCnn.ConnectionString = GetODBCLessCnnString() '' Open connection to Oracle
ObjCnn.ConnectionString = GetODBCCnnString() MsgBox "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Descriptionįor testing, add the code below to the first button's subroutine.įunction AddDataToOracle() As Integer On Error Goto Catch '' Test result If intRecAffected2 1 Then '' Adding data to Country table failedĪddDataToAccess = 2 '' Indicates the second insert failed Exit Function '' Exit the function immediately End If '' So far so good, we commit the transaction.ĪddDataToAccess = -1 '' Successful '' Close the connection and clean upĪddDataToAccess = 3 '' Some other error occurred ObjCnn.RollbackTrans '' Roll back transactionĪddDataToAccess = 1 '' Indicates the first insert failed Exit Function '' Exit the function immediately End If '' Now we can continue to execute the second INSERT query '' Test result If intRecAffected1 1 Then '' Adding data to Contact table failed StrSQL2 = "insert into Country(ID, Country_Name) values(1, 'France')" StrSQL1 = "insert into Contact(ID, Contact_Name) values (1, 'John')" '' Establish connection to the Access database's JET engine. If you are not familiar with ADO's Execute method, here is Microsoft Reference page aboutįunction AddDataToAccess() As Integer On Error Goto Catchĭim strSQL1 As String Dim strSQL2 As String Dim intRecAffected1 As Integer Dim intRecAffected2 As Integer Dim objCnn As ADODB.Connection Otherwise, it rolls back the transaction so nothing is If the first INSERT succeeds, it willĬontinue to do the second INSERT.
#Setting up microsoft access database how to
This function shows how to do transaction processing in an Access database.ĭata is inserted into two tables - Contact and Country. In VBA editor, click Tools -> References.ĭemo 1: Transaction processing inside Access database (any version from 2.0 to 2.8) in Northwind database. To get the code on this page to work, you need to add a reference to Microsoft ActiveX Data Objects Library Here is the structure for the two tables used in the demo code on this page. To the DBMS is through an ODBC or ODBC-less connection and executed by using ADO Connection object. In this case, transaction is managed by the DBMS rather than by Access.
Second, Access is able to pass transaction-based operations to a backend DBMS system such as accdb file), this is becauseĪccess JET engine inherently supports transaction based processing. That is, operations like INSERT, UPDATE,Īnd DELETE run inside the same Access database (the same. There are two aspects of transactional support in Access.įirst, the transaction can happen inside Access JET Engine. If errors occur in any part of step 2, then rollback the transaction.If no errors occur in any part of step 2, then commit the transaction.Execute a set of data manipulations (via SQL queries such as insert, update, or delete).Either all of the data modificationsĪre carried out in the database, or nothing is carried out.Ī simple ACID (atomic, consistent, isolated and durable) database transaction does the following four steps:
It 's able to do transactional operations in an "all-or-nothing" fashion, just like any other mainstreamĭBMS systems out there such as Oracle, MySQL, SQL Server, etc. MS Access is an all-in-one and powerful development environment for office-based applications. How to do transaction based processing in MS Access