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

Open in new window

CMI_ITAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CMI_ITConnect With a Mentor Author Commented:
Found my own solution.  In Access, changed the call to use this:
      Dim con As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim cmd As ADODB.Command
 
      Dim res As String
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = MFGdB_CONN
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "GetProcessSelection"
      cmd.Parameters.Append cmd.CreateParameter("wo_nbr", adVarChar, adParamInput, 18, Me.cboWorkOrder)
      cmd.Parameters.Append cmd.CreateParameter("pt_part", adVarChar, adParamInput, 18, Me.cbo_pt_part)
      cmd.Parameters.Append cmd.CreateParameter("ProcessID", adVarChar, adParamOutput, 30)
      cmd.Parameters.Append cmd.CreateParameter("ProcessSelectID", adVarChar, adParamOutput, 30)
      cmd.Parameters.Append cmd.CreateParameter("DesCapID", adVarChar, adParamOutput, 30)
      cmd.Execute
      strProcessID = cmd("ProcessID")

Open in new window

0
 
QPRCommented:
0
 
CMI_ITAuthor Commented:
I don't think so.  If I leave the Access code the way it is shown above, but I change the select in stored proc as shown below no Access error message.

If changed to:
    SELECT 
        Process_ID
      , Process_Select_ID
      , @intDesCapID 
    FROM Process_selection 
    WHERE pt_part = @pt_part
 
Instead of:
    SELECT 
        @ProcessID = Process_ID
      , @ProcessSelectID = Process_Select_ID
      , @DesCapID = @intDesCapID 
    FROM Process_selection 
    WHERE pt_part = @pt_part

Open in new window

0
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.