ACC: Cannot Open Recordset on SQL Server Inside Transaction

ID: Q131342

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

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=<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()

STATUS

This behavior is by design.

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.

REFERENCES

For more information about the OpenRecordSet method, search the Help Index for OpenRecordSet method.

For more information about the OpenDatabase method, search the Help Index for OpenDatabase method.

Microsoft Access "Building Applications," version 2.0, "Using Transactions to Control Changes," pages 265-267

Additional query words:

Keywords : kbusage OdbcSqlms
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbprb