Microsoft KB Archive/131342

= ACC: Cannot Open Recordset on SQL Server Inside Transaction =

Article ID: 131342

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q131342



SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you open a recordset on a SQL Server table, you receive the following error message:

 SQL Server Version 4.2x

Microsoft Access 7.0 and 97:

Run-time error '3146'

Application-defined or object-defined error

 SQL Server Version 4.2x and 6.x

Microsoft Access version 2.0:

ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server] stored procedure sp_statistics cannot be run while in a transaction (#20001)



This error does not occur when you use Microsoft Access 7.0 or 97 with SQL Server version 6.x.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



CAUSE
You used the OpenDatabase method to opened the recordset while a transaction was pending.



RESOLUTION
You can either link (attach) the SQL Server table and open the recordset on the linked table, or you can open the recordset outside of a pending transaction.

To open the recordset outside of a pending transaction, follow these steps:

 Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).  Create a module and type the following line in the Declarations section if it is not already there: Option Explicit   Type the following procedure:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Function Trans_Work Dim db As Database Dim ws As WorkSpace Dim rs As Recordset Dim connectstring As String connectstring = "ODBC;DSN= ;UID= ;_                        PWD= ;DATABASE=PUBS"

' NOTE: In the "connectstring" line above replace <datasource ' name> with the name of your data source for SQL Server; replace ' with the username used to log on to the data ' source; and replace with the appropriate password.

On Error GoTo Trans_Work_Err Set ws = dbengine.Workspaces(0) Set db = ws.OpenDatabase("", False, False, connectstring) Set rs = db.OpenRecordset("dbo.authors")  'Opens the recordset. ws.BeginTrans  'Starts the transaction. rs.MoveLast Debug.Print rs![au_lname] ws.CommitTrans  'Commits the transaction. rs.Close 'Closes the Recordset. db.Close Exit Function

Trans_Work_Err: ws.Rollback If Err = 3146 Then 'ODBC call failed Error (Err) Else MsgBox Error$  'The message if a different error occurs. End If        Exit Function End Function </li> Type the following line in the Debug window (or Immediate window in version 2.0), and press ENTER:

? Trans_Work

</li></ol>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
The SQL Server driver provided with Microsoft Access calls a SQL Server catalog stored procedure called SP_STATISTICS to retrieve information about the table on which you create the recordset. SQL Server does not allow this stored procedure to run while a transaction is pending.

Steps to Reproduce Behavior
<ol> Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).</li>  Create a module and type the following line in the Declarations section if it is not already there: Option Explicit </li>  Type the following procedure:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Function Trans_Fail Dim db As Database Dim ws As WorkSpace Dim rs As Recordset Dim connectstring As String connectstring = "ODBC;DSN= ;UID= ;_                           PWD= ;DATABASE=PUBS"

' NOTE: For the "connectstring" line above replace <datasource ' name> with the name of your data source for SQL Server; ' replace with the username used to log on to the data ' source; and replace with the appropriate password.

On Error GoTo Trans_Fail_Err Set ws = dbengine.Workspaces(0) Set db = ws.OpenDatabase("", False, False, connectstring) ws.BeginTrans  'Starts the transaction. Set rs = db.OpenRecordset("dbo.authors")  'Opens the recordset. rs.MoveLast Debug.Print rs![au_lname] ws.CommitTrans  'Commits the transaction. rs.Close 'Closes the Recordset. db.Close Exit Function

Trans_Fail_Err: ws.Rollback If Err = 3146 Then 'ODBC call failed Error (Err) Else MsgBox Error$  'The message if a different error occurs. End If        Exit Function End Function </li> Type the following line in the Debug Window (or Immediate window in version 2.0)and press ENTER:

? Trans_Fail

</li></ol>

Note that when the recordset is opened, you receive the error message described in the "Symptoms" section.

<div class="references_section">