multiple recordsets - error 3704

[Timtropolis]

Greetings all,

Have a unique scenario here.
I am calling a stored procedure in SQL Server 2008 from my Access 2010 database. (see my code below).

Dim objConnection As New ADODB.Connection
    Dim objCom As ADODB.Command
    Dim x, y As Variant
    Dim rst, rst2 As ADODB.Recordset
        
    Set objCom = New ADODB.Command
    
    objConnection.Provider = "sqloledb"
    provStr = "Data Source=Server01;" & "Initial Catalog=xxxx;
        User Id=xxxx;Password=xxxx;"
    objConnection.Open provStr
    
    x = 38600 'for testing purposes
    
    With objCom
        .ActiveConnection = objConnection
        .CommandText = "pr_GetOrder (" & x & ")"
        .CommandType = adCmdStoredProc
        Set rst = .Execute
    End With

    [COLOR="Red"]rst.MoveFirst [/COLOR]<-- Run-time error '3704':
Operation is not allowed when the object is closed.

The stored proc returns two completely different recordsets. When I try
to reference the recordset I get the 3704 error.

My question: Is there any way Access can reference the recordsets created by the SQL Server proc? I've tried using indexing when declaring the recordsets as well as the .NextRecordSet method to no avail.

Any help would be most welcome.
TIA,
Tim

 

[Guus2005]

In your code:

Dim objConnection As New ADODB.Connection
    Dim objCom As ADODB.Command
    Dim x, y As Variant                         'Both variant. 
    Dim rst, rst2 As ADODB.Recordset  ' rst = variant, rst2 = ADODB.Recordset

use the following instead:

Dim objConnection As New ADODB.Connection
    Dim objCom As ADODB.Command
    Dim lngX as Long                   'lngX=long. Y not used. Don't use variant (slow)
    Dim rst as ADODB.recordset, rst2 As ADODB.Recordset  ' both = ADODB.Recordset

The rest of your code:

    Set objCom = New ADODB.Command
    
    objConnection.Provider = "sqloledb"
    provStr = "Data Source=Server01;" & "Initial Catalog=xxxx;
        User Id=xxxx;Password=xxxx;"
    objConnection.Open provStr
    
    lngX = 38600 'for testing purposes
    
    With objCom
        .ActiveConnection = objConnection
        .CommandText = "pr_GetOrder (" & cstr(lngX) & ")"
        .CommandType = adCmdStoredProc
        Set rst = .Execute
    End With

    If not rst.EOF then
        rst.MoveFirst
    end if

HTH

 

[Timtropolis]

Thx Guus, but that didn't resolve the problem.

Anyone else?