[MICROSOFT] [ODBC SQL SERVER DRIVER] Connection is busy
with the results for another hstmt.

Prior to calling the Subroutine below I have created the connection
handle made the connection to the database.
The error happens when the second SQLExecDirect statement is called
using another, but different,  hstmt (hacc_desc).  This second
SQLExecDirect statement is inside of a loop that is Fetching rows from
the result of the first SQLExecDirect statement.
I have seen information which suggests it is possible but I am not
sure what I am missing at the moment.  

Sub GetData()

Dim hstmt As Long
Dim hacc_desc As Long

Dim iSQLCommandLen As Long
Dim iSQLQueryLen As Long

Dim sBuff As String
Dim sQueryString As String             'Query string to get account
description information
Dim lOutlen As Long

Dim iRC As Integer
Dim iNumCols As Integer
Dim bPrint_Acct As Integer
Dim I, iRow, iRange_Start As Integer
Dim sSave_Doc, sSave_Acct As String

Dim DataSheet As Worksheet

    On Error GoTo AddResultSheet

    Sheets("Query_Result").Delete

AddResultSheet:

    Sheets.Add Type:=xlWorksheet
    ActiveSheet.Name = "Query_Result"
    On Error GoTo 0

'Allocate memory for a statement handle(hstmt) and associates the
statement handle
'with the connection specified by hdbc. iRC stores a numeric code that
returned by SQLAllocStmt
iRC = SQLAllocStmt(hdbc, hstmt)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to allocate statement")
    End
End If

'sets options related to an hstmt.
iRC = SQLSetStmtOption(hstmt, SQL_ASYNC_ENABLE, 0)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to set SQL Async Option")
    End
End If

'*********************************************************************************************
'Allocate memory for a statement handle(hacc_desc) and associates the
statement handle
'with the connection specified by hdbc. iRC stores a numeric code that
returned by SQLAllocStmt
iRC = SQLAllocStmt(hdbc, hacc_desc)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to allocate account description statement")
    End
End If

'sets options related to an hstmt.
iRC = SQLSetStmtOption(hacc_desc, SQL_ASYNC_ENABLE, 0)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to set SQL Account description Async Option")
    End
End If
'*********************************************************************************************

iSQLCommandLen = Len(sSQLCommand)

iRC = SQLExecDirect(hstmt, sSQLCommand, iSQLCommandLen)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to execute statement")
    DescribeError henv, hstmt
    End
End If

iRC = SQLNumResultCols(hstmt, iNumCols)

If iRC <> SQL_SUCCESS Then
    MsgBox ("Unable to retrieve column count")
    End
End If

iRow = 7
iRange_Start = 7

Do While SQLFetch(hstmt) = SQL_SUCCESS

    sBuff = String$(255, 0)

    For I = 1 To iNumCols

        iRC = SQLGetData(hstmt, I, SQL_CHAR, ByVal sBuff, 255,
lOutlen)

        If iRC <> SQL_SUCCESS Then
           DescribeError hdbc, hstmt
           End
        End If

        Select Case I

            'Test to see if account number has changed, if so , do
subtotals
            Case 1
                If sSave_Acct <> Left$(sBuff, lOutlen) Then
                    If iRow <> 7 Then

                        Insert_Totals iRow, iRange_Start, iRow - 1

                        iRow = iRow + 2
                        sSave_Acct = Left$(sBuff, lOutlen)
                        iRange_Start = iRow
                    End If

                    sSave_Acct = Left$(sBuff, lOutlen)
                    bPrint_Acct = 1
                End If

            'Test for "print flag" - if so, print account number
            Case 3, 4, 5
                If bPrint_Acct = 1 Then
                    Sheets("Query_Result").Cells(iRow, I - 2).Value =
Left$(sBuff, lOutlen)
                End If

                If I = 5 Then
                    'Get the account description and increment the row
counter

'***************************************************************
                    sQueryString = "SELECT account_description FROM
FINANCIAL.dbo.glchart WHERE account_code = " + sSave_Acct
                    iSQLQueryLen = Len(sQueryString)

                    'Execute the query on the database
                    iRC = SQLExecDirect(hacc_desc, sQueryString,
iSQLQueryLen)

                    If iRC <> SQL_SUCCESS Then
                       MsgBox ("Unable to execute statement")
                       DescribeError henv, hstmt
                       End
                    End If

                    'Fetch the data
                    SQLFetch (hacc_desc)
                    iRC = SQLGetData(hacc_desc, 1, SQL_CHAR, ByVal
sBuff, 255, lOutlen)

                    If iRC <> SQL_SUCCESS Then
                       DescribeError hdbc, hstmt
                       End
                    End If
                    iRC = SQLFreeStmt(hacc_desc, SQL_CLOSE)

                    'Put the data in the sheet
                    Sheets("Query_Result").Cells(iRow, I - 1).Value =
Left$(sBuff, lOutlen)
                    iRow = iRow + 1

'***************************************************************
                    bPrint_Acct = 0
                End If

            'Check for matching GST and JCN entries, print on same
line
            Case 2
                If (Left$(sBuff, 3) = "JCN") Then
                    If (Mid$(sBuff, 4, 6) = Mid$(sSave_Doc, 4, 6))
Then
                        iRow = iRow - 1
                    End If
                End If

                sSave_Doc = Left$(sBuff, lOutlen)
                Sheets("Query_Result").Cells(iRow, 10).Value =
Left$(sBuff, lOutlen)

            'Put credits and debits in respective columns
            Case 8
                If Left$(sBuff, 1) = "-" Then
                    Sheets("Query_Result").Cells(iRow, 7).Value =
Left$(sBuff, lOutlen)
                Else
                    Sheets("Query_Result").Cells(iRow, 6).Value =
Left$(sBuff, lOutlen)
                End If

                Sheets("Query_Result").Cells(iRow, 8).Value = _
                    Sheets("Query_Result").Cells(iRow, 6).Value -
Sheets("Query_Result").Cells(iRow, 7).Value

            Case 9
                Sheets("Query_Result").Cells(iRow, 9).Value =
Left$(sBuff, lOutlen)

            Case Else
                Sheets("Query_Result").Cells(iRow, I - 2).Value =
Left$(sBuff, lOutlen)

        End Select

    Next I

    iRow = iRow + 1
Loop

Insert_Totals iRow, iRange_Start, iRow - 1

iRC = SQLFreeStmt(hstmt, SQL_DROP)
iRC = SQLFreeStmt(hacc_desc, SQL_DROP)
Format_Report

End Sub

*******************************************
* David Lawrence


*******************************************

[Debug]
RmtTrace=16

This causes ODBC to be put into "synchronous mode". You might
want to take a look at SQLSetStmtOption's SQL_ASYNC_ENABLE
option. Maybe it needs to be explicitly turned off?

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!

I have an application which uses two global connections plus (at least) two
per open MDI window. Its not hard for it to generate ten connections per
user. Make sure you set a high value for the number of connections allowed
on the server side!

Hope this helps,

James Shields

Thanks for the reply.  This is my first of many ODBC API projects,  So
you will have to excuse me If I pick your brain a bit.

Thank you very much
David Lawrence