PRB: VB ODBC Error "Connection is busy with results..."

ID: Q119023

The information in this article applies to:

SUMMARY

When accessing an SQL Server database as an ODBC data source in Visual Basic versions 3.0 or 4.0 with the Microsoft Access version 2.0 Compatibility Layer installed, error message 3146 may occur:

ODBC-call failed. [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt [#0]

This error is the result of the SQL Server ODBC driver. The driver can only handle one active statement at a time. The statement remains active until all the rows are fetched.


MORE INFORMATION

This problem occurs because of a change in the behavior of the Microsoft Access engine between versions 1.1 and 2.0. In Visual Basic version 3.0, as shipped with the database engine in Microsoft Access version 1.1, when the DB_SQLPASSTHROUGH flag was used with CreateSnapshot, CreateDynaset, or as a setting for the Options property of the data control, the engine fully populated all result sets before the next Visual Basic statement could be executed.

A fully populated result set means that all the rows or records in the result set have been visited and fetched to the client machine. This was accomplished by doing the equivalent of a <your data access object>.MoveLast implicitly before the method (CreateDynaset, CreateSnapshot, <your data control>.Refresh or the creation of the form with the data control) returned.

Using the Compatibility Layer and the database engine in Microsoft Access version 2.0, this automatic forcing of a fully populated result set no longer occurs. This design decision was made because fully populating result sets can be expensive in terms of performance. It was decided to allow the programmer the control over whether the result set is fully populated. This gives the programmer the flexibility of choosing whether to fully populate the result set and when, either by slow navigation or in an explicit <your data access object>.MoveLast at a point after the initial object creation.

This error occurs when there are pending results on a statement handle that is then used to execute another query. This causes a problem when the ODBC data source is a SQL Server (Microsoft or Sybase) because, owing to the architectural design, there can be only one active statement per connection on an SQL Server.

Therefore the SQL Server ODBC driver (SQLSRVR.DLL) cannot allow multiple active HSTMTs on a single connection handle or HDBC. An active statement is defined as a statement that has pending results; that is, the whole result set has not been read from the server.

When a result set is created with the DB_SQLPASSTHROUGH flag, an HSTMT, which is an ODBC statement handle, is tied up waiting on the pending fetch of the entire result set. If there are pending results on a connection when the next query is executed, the error occurs.

When the DB_SQLPASSTHROUGH flag is NOT used, the connection manager component of the engine creates additional connections for each of the statements, because these statements may be pending throughout the life of the data access object (DAO). In the case of dynasets or data controls (which are wrappers for a database object and a dynaset), because these represent updateable result sets, a two-way connection must be maintained.

NOTE: These connections (HDBCs) can be recycled by the connection manager as they go idle. The time they are maintained is a function of activity and the ConnectionTimeout setting in the [ODBC] section of the VB.INI or <your exename>.INI file. See the following articles in the Microsoft Knowledge Base for more information:

Q110227 : PRB: ODBC Database Remains Open After a Close Method Used

Q115237 : How to Use Temporary Tables in SQL Server from Visual Basic 3.0


WORKAROUND

The solution for the changed behavior of the Microsoft Access 2.0 engine is to add an explicit <your data access object>.MoveLast after each query executed using DB_SQLPASSTHROUGH thereby forcing full population of the result set. This will be no slower than the original Visual Basic version 3.0 performance and possibly faster, and it will prevent the error from occurring.

For Microsoft Access version 1.1 compatibility, placing an extra (redundant) <your data access object>.MoveLast after the creation of the DAO will not cost any more, because the result set is already fully populated automatically.

CODE EXAMPLE

Use the following code to demonstrate and work around the problem (NOTE: all statements must be complete on one line): 

   Dim db As database, sn As snapshot, sn2 As snapshot
   Dim sql As String

   Set db = OpenDatabase("", 0, 0,
    "odbc;dsn=texas;uid=sa;pwd=;database=library;")

   sql = "select * from adultwide where member_no < 2001"
   Set sn = db.CreateSnapshot(sql, 64)

   ' Uncomment the next line to work around the problem.
   ' sn.MoveLast

   sql = "select * from adultwide where member_no < 2001"
   Set sn2 = db.CreateSnapshot(sql)
   ' This second CreateSnapshot causes the error:
   '  ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Connection
   '  is busy with results for another hstmt (#0).

Use the following code to trap the error: 

   Dim db As database, sn As snapshot, sn2 As snapshot
   Dim sql As String

   On Error GoTo handle

   Set db = OpenDatabase("", 0,
   0,"odbc;dsn=texas;uid=sa;pwd=;database=library;"
   )

   sql = "select * from adultwide where member_no < 2001"
   Set sn = db.CreateSnapshot(sql, 64)

   sql = "select * from adultwide where member_no < 2001"
   Set sn2 = db.CreateSnapshot(sql)

   Exit Sub
   handle:
   ' This local error handler could call a central global handler
   ' and use a global object pointer (Global glbsn As snapshot) to
   ' track any pending snapshots; for example:
   '   Set sn = db.CreateSnapshot(sql, 64)
   '   Set glbsn=sn
   ' Then in the handler, do => glbsn.MoveLast

   sn.MoveLast
   Resume

Additional query words: 3.00 4.00 vb4all vb4win 


Keywords          : kbDatabase kbODBC 
Version           : 3.00 4.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: May 28, 1999