Solved

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

Posted on 2008-06-12
3
1,503 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now