ACC: Cannot Open Recordset on SQL Server Inside Transaction

ID: Q131342

The information in this article applies to:

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:

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:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create a module and type the following line in the Declarations 

   section if it is not already there:

      Option Explicit
3. 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
4. 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

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. Create a module and type the following line in the Declarations section 

   if it is not already there:

      Option Explicit
3. 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=<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
4. 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           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb

Last Reviewed: November 21, 1998