ACC: Cannot Open Recordset on SQL Server Inside Transaction (131342)

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.

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=<datasource name>;UID=<username>;_
                         PWD=<password>;DATABASE=PUBS"

         ' NOTE: In the "connectstring" line above replace <datasource
         ' name> with the name of your data source for SQL Server; replace
         ' <username> with the username used to log on to the data
         ' source; and replace <password> 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

Type the following line in the Debug window (or Immediate window in version 2.0), and press ENTER:
? Trans_Work()

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

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
```

      Function Trans_Fail ()
         Dim db As Database
         Dim ws As WorkSpace
         Dim rs As Recordset
         Dim connectstring As String
         connectstring = "ODBC;DSN=<datasource name>;UID=<username>;_
                            PWD=<password>;DATABASE=PUBS"

         ' NOTE: For the "connectstring" line above replace <datasource
         ' name> with the name of your data source for SQL Server;
         ' replace <username> with the username used to log on to the data
         ' source; and replace <password> 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

Type the following line in the Debug Window (or Immediate window in version 2.0)and press ENTER:
? Trans_Fail()

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

ACC: Cannot Open Recordset on SQL Server Inside Transaction (131342)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

MORE INFORMATION

Steps to Reproduce Behavior

REFERENCES