hstmt error using RDO to call SQL from VB

When calling a SQl stored proc using RDO I get:

RunTime Error 40002
S1000: {Microsoft}{ODBC SQL Server Driver}  Connection is busy with results for another hstmt

I know what an hstmt is (roughly!) but not how to solve this.  I tried looping while rs.StillExecuting = True, and tested the other defined recordset in my routine from the command line, and neither where executing while the error came up.

Can you help???
Who is Participating?

Improve company productivity with a Business Account.Sign Up

connexConnect With a Mentor Commented:
Ok found the following in the KB:

PRB: VB ODBC Error "Connection is busy with results..."
Last reviewed: March 29, 1996
Article ID: Q119023  
The information in this article applies to:
Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0
Professional Edition of Microsoft Visual Basic for Windows, version 3.0
Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer

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.

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:

   ARTICLE-ID: Q110227
   TITLE     : PRB: ODBC Database Remains Open After a Close Method Used

   ARTICLE-ID: Q115237
   TITLE     : How to Use Temporary Tables in SQL Server from Visual Basic

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.

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,

   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,

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


oops forgot one .)

see Q143032 in the KB too. (http://support.microsoft.com/kb/)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.