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

Posted on 2008-06-12
Last Modified: 2013-11-27
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!!

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
   @intDesCapID INTEGER
  -- SET NOCOUNT to ON to no longer display the count message.
  --Check to see if DesCap is required
  EXEC GetDesCapID @wo_nbr, @pt_part, @intDesCapID OUTPUT
  IF @intDesCapID IS NULL --DesCap is NOT required
        @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
  IF @intDesCapID IS NOT NULL --DesCap is required
        @ProcessID = Process_ID
      , @ProcessSelectID = Process_Select_ID
      , @DesCapID = @intDesCapID 
    FROM Process_selection
      Process_selection.pt_part = @pt_part
      AND Process_selection.Tip =
            (SELECT DesCap.Tip
             FROM DesCap
               DesCap.DesCap_ID = 
                 (SELECT wo_descap
                  FROM wo_mstr
                    wo_nbr = @wo_nbr
                    AND wo_part = @pt_part
  END -- IF @intDesCapID IS NOT NULL --DesCap is required
  -- Reset SET NOCOUNT to OFF
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

Question by:CMI_IT
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 29

Expert Comment

ID: 21775078

Author Comment

ID: 21775121
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:
      , Process_Select_ID
      , @intDesCapID 
    FROM Process_selection 
    WHERE pt_part = @pt_part
Instead of:
        @ProcessID = Process_ID
      , @ProcessSelectID = Process_Select_ID
      , @DesCapID = @intDesCapID 
    FROM Process_selection 
    WHERE pt_part = @pt_part

Open in new window


Accepted Solution

CMI_IT earned 0 total points
ID: 21861433
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)
      strProcessID = cmd("ProcessID")

Open in new window


Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
office 2016 license quastion 3 45
Batch convert csv to xlsx 10 60
Convert MS Access Excel Routine to Late Binding 6 25
Query Task 8 21
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question