Solved

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

Posted on 2008-06-12
3
1,511 Views
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!!
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

0
Comment
Question by:CMI_IT
  • 2
3 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 21775078
0
 

Author Comment

by:CMI_IT
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:
    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
 

Accepted Solution

by:
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)
      cmd.Execute
      strProcessID = cmd("ProcessID")

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 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