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

• Microsoft Open Database Connectivity, versions 1.0, 2.0, 3.0, 3.5
• Microsoft OLE DB, versions 1.0, 1.1, 1.5
• ActiveX Data Objects (ADO), versions 1.0, 1.5

SUMMARY

Neither Open Database Connectivity (ODBC), nor any released Microsoft OLE DB Provider, supports Nested Transactions. ActiveX Data Objects (ADO) supports the feature, but only if the underlying provider exposes it. Currently none of Microsoft's OLE DB providers support Nested Transactions.

MORE INFORMATION

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.

   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=sa;pwd=;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.

   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

REFERENCES

http://www.microsoft.com/data

The Microsoft Jet Database Engine Programmer's Guide (Second Edition), page 425 – "Nesting of transactions", MS Press.

Keywords          : kbinterop
Technology        : odbc ole
Version           : WINDOWS:1.0,1.1,1.5,2.0,3.0,3.5
Platform          : WINDOWS
Issue type        : kbinfo