You are currently offline, waiting for your internet to reconnect

INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO

This article was previously published under Q177138
This article has been archived. It is offered "as is" and will no longer be updated.
Neither Open Database Connectivity (ODBC), nor Microsoft OLE DB Provider, supports Nested Transactions. ActiveX Data Objects (ADO) supports the feature, but only if the underlying provider exposes it. Currently only the Jet OLE DB provider supports Nested Transactions.
Neither ODBC, nor any released OLE DB Provider, exposes Nested Transaction functionality, even if the back-end data source supports this feature.

Nested Transactions are a feature of the Jet engine, but only when accessing the Jet engine directly via Data Access Objects (DAO). If using DAO against a Microsoft database (MDB) file that has an attached ODBC Data Source, any attempt to use nested transactions is ignored.

The ADO Help indicates that ADO supports nested transactions but only if the underlying OLE DB Provider supports nested transactions. The following Microsoft Providers do not support Nested Transactions: OLE DB Provider for ODBC Drivers
Index Server Provider for OLE DB
Active Directory Provider for OLE DB

Sample Code

Below is the error message and the code snippet that generates the error for ADO when the underlying provider does not support nested transactions:
Run-time error '-2147168237 (8004d013)':
Only one transaction can be active on this session.
Here is the code used to generate the error:
   Option Explicit   Dim Cn As ADODB.Connection   Dim Cmd1 As ADODB.Command   Dim strConn, strQSQL As String   Private Sub Form_Load()   ' Open connection.  Set Cn = New ADODB.Connection   ' Change the connect string to match your setup   strConn = "driver={SQL Server};server=MyServer;" & _   "uid=<username>;pwd=<strong password>;database=pubs"   Set Cn = New ADODB.Connection   Cn.Open strConn   Cn.CursorLocation = adUseClient   'Cmd1 executes a SQL Insert   strQSQL = "Insert INTO Stores (stor_id, stor_name, " & _   "stor_address, city) VALUES(?,?,?,?)"   Set Cmd1 = New ADODB.Command   With Cmd1   .ActiveConnection = Cn   .CommandText = strQSQL   .CommandType = adCmdText   .Parameters.Append .CreateParameter("", adChar, adParamInput, 4)   .Parameters.Append .CreateParameter("", adVarChar, adParamInput, 40)   .Parameters.Append .CreateParameter("", adVarChar, adParamInput, 40)   .Parameters.Append .CreateParameter("", adVarChar, adParamInput, 20)   End With   End Sub   Private Sub cmdInsert_Click()   MousePointer = vbHourglass   Cn.BeginTrans   Cmd1(0) = "101"   Cmd1(1) = "Store One"   Cmd1(2) = "123 Oak St."   Cmd1(3) = "Seattle"   Cmd1.Execute   Cn.BeginTrans   'Comment out and this code will run and if you do so,                   'Make sure that you delete the record that this sample                   'adds to the database.   Cmd1(0) = "102"   Cmd1(1) = "Store Two"   Cmd1(2) = "123 Main St."   Cmd1(3) = "Tacoma"   Cmd1.Execute   Cn.CommitTrans   'Comment out and this code will run.   Cn.CommitTrans   MousePointer = vbDefault   End Sub   Private Sub Form_Unload(Cancel As Integer)   Cn.Close   Set Cn = Nothing   Set Cmd1 = Nothing   End Sub
The Microsoft Jet Database Engine Programmer's Guide (Second Edition), page 425 - "Nesting of transactions", MS Press.

Article ID: 177138 - Last Review: 12/05/2015 08:11:34 - Revision: 4.2

Microsoft Data Access Components 1.5, Microsoft Data Access Components 2.0, Microsoft Data Access Components 2.1, Microsoft Data Access Components 2.5, Microsoft Data Access Components 2.6, Microsoft Data Access Components 2.7, Microsoft Data Access Components 2.8

  • kbnosurvey kbarchive kbdatabase kbinfo kbinterop KB177138