Access 2000 Run-time error '3704' when executing SQL 2000 stored procedure with output parameter

Hi All,

I hope this is clear enough for someone to understand because I am at a standstill without help.

I have inherited an Access 2000 "app" that is the front-end to a SQL 2000 database back-end.  I am changing some of the VB code to execute a stored procedure instead of having inline SQL.  In addition, the new select statement has three output parameters.  

When I execute the stored procedure from Query Analyzer with the following code, it runs successfully.  But when I run it from Access VB I get an error message:
Run-time error '3704':
Operation is not allowed when the object is closed.

I am attaching a code snippet with comments explaining what I have tested.  the code is separated by ========== blocks.

In advance, thanks!!
cmi_it

The code below fails at the If Not ado_rs.EOF Then line:
****************************************************************
Let ado_sql = "GetProcessSelection '" & Me.cboWorkOrder & "', '" & Me.cbo_pt_part & "', intProcessID, intProcessSelectID, intDesCapID"
 
ado_rs.Open ado_sql, dB_CONN, adOpenForwardOnly, adCmdText
If Not ado_rs.EOF Then
  Do While Not ado_rs.EOF
    strProcessID = ado_rs(0)
...
===============================================================
 
The stored procedure code is below.  
===============================================================
ALTER PROCEDURE [dbo].[GetProcessSelection]
    @wo_nbr	   VARCHAR(18)
  , @pt_part	   VARCHAR(18)
  , @ProcessID       NVARCHAR(30) OUTPUT
  , @ProcessSelectID varchar(30) output
  , @DesCapID        varchar(30) output
 
AS
DECLARE
   @intDesCapID INTEGER
 
BEGIN
  -- SET NOCOUNT to ON to no longer display the count message.
  SET NOCOUNT ON;
 
  --Check to see if DesCap is required
  EXEC GetDesCapID @wo_nbr, @pt_part, @intDesCapID OUTPUT
 
  IF @intDesCapID IS NULL --DesCap is NOT required
  BEGIN
    SELECT 
        @ProcessID = Process_ID
      , @ProcessSelectID = Process_Select_ID
      , @DesCapID = @intDesCapID 
    FROM Process_selection 
    WHERE pt_part = @pt_part
  END --IF @intDesCapID IS NULL --DesCap is NOT required
  ELSE
  IF @intDesCapID IS NOT NULL --DesCap is required
  BEGIN
    SELECT 
        @ProcessID = Process_ID
      , @ProcessSelectID = Process_Select_ID
      , @DesCapID = @intDesCapID 
    FROM Process_selection
    WHERE 
      Process_selection.pt_part = @pt_part
      AND Process_selection.Tip =
            (SELECT DesCap.Tip
             FROM DesCap
             WHERE 
               DesCap.DesCap_ID = 
                 (SELECT wo_descap
                  FROM wo_mstr
                  WHERE 
                    wo_nbr = @wo_nbr
                    AND wo_part = @pt_part
                 )
             )
  END -- IF @intDesCapID IS NOT NULL --DesCap is required
 
  -- Reset SET NOCOUNT to OFF
  SET NOCOUNT OFF;
 
END
===============================================================
As you can see the stored procedure calls another stored procedure which also has an output parameter.  When I execute the GetProcessSelection stored procedure with the following statement in Query Analyzer it runs successfully:
===============================================================
exec GetProcessSelection '130907', 'ep-08', @strProcessID output, @intProcessSelectID output, @intDesCapID output
===============================================================

Select allOpen in new window