Do stored procedures need to be closed?

[bradmaunsell]

I am new to ADP and wonder if stored procedures need to be closed like closing recordset in a "regualar" mdb application. That is, in a regular application, I always close my recordsets with the following two lines of VBA.

rs.close
set rs=nothing

Below is some VBA code I am using to set a (stored procedure) recordset for a form in my new ADP application. This code works fine.

Do I need to close the form recordset to release the resources?

Thanks,

Brad
==========================================================

Public Sub LoadFormRecordset(frm As Form, lngKeyPara As Long, stSP As String)
Dim Param1 As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rs As New ADODB.Recordset

Set Param1 = CreateObject("ADODB.Parameter")
Param1.Direction = adParamInput
Param1.Type = adInteger
Param1.Value = lngKeyPara

Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc

cmd.CommandText = stSP
cmd.Parameters.Append Param1
Set rs = CreateObject("ADODB.Recordset")

rs.CursorLocation = adUseServer 'CursorLocation must be set before setting connection - not affect existing (open connection)
cmd.ActiveConnection = CurrentProject.Connection
Call rs.Open(cmd, , adOpenDynamic, adLockOptimistic)
Set frm.Recordset = rs

End Sub

 

[genomon]

Stored procedure? This looks like code from a standard module. It also looks like the rs should be closed...

Always remember that you're unique. Just like everyone else.

 

[bradmaunsell]

Perhaps I should have included more info. This is in fact code used to run a stored procedure that is used as a recordset for an access bound form.

The code is called with this line

LoadFormRecordset Me, Parent!AgencyID, "sp_Agt_Page_Producers"

And here is the actual stored procedure that runs on the server (SQL Express).

This all works just fine but I haven't figured out how to "close" the pstored procedure - if there is any such thing as closing a stored procedure (as mentioned in first part of this string of notes)



USE [MGA]
GO
/****** Object: StoredProcedure [dbo].[sp_Agt_Page_Producers] Script Date: 11/06/2007 16:38:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Agt_Page_Producers]
@AgtID as int
AS
BEGIN
SELECT TOP (100) PERCENT dbo.tblAgencyProducers.ProducerID, dbo.tblAgencyProducers.AgencyID,
dbo.tblAgencyProducers.Prefix,
dbo.tblAgencyProducers.FirstName, dbo.tblAgencyProducers.MiddleInitial,dbo.tblAgencyProducers.LastName,
dbo.tblAgencyProducers.Suffix,
dbo.tblAgencyProducers.Title, dbo.tblAgencyProducers.Salutation,
dbo.tblAgencyProducers.EmailAddress,
dbo.tblAgencyProducers.InActive
FROM dbo.tblAgencyProducers

WHERE (dbo.tblAgencyProducers.AgencyID = @AgtID)

ORDER BY dbo.tblAgencyProducers.LastName
END

 

[genomon]

rs.close
set rs=nothing

Gotcha. The sp is fine, but the rs needs to be closed in the calling code (Public Sub LoadFormRecordset).

Always remember that you're unique. Just like everyone else.

 

[bradmaunsell]

I added that code to the UNLOAD event and get an error.

Perhaps I still need to offer more info.

This form has a main form in the header section. The detail has a tab control with 6 or 7 tabs. Each tab has a subform on it that links to the header with a key "AppID" common to all forms.

Obviously when the main form is closed, all of the subforms get closed as well.

I have placed the rs.close ans set rs = nothing in the UNLOAD of the SUBFORM.

So, based on what you are saying, I do in fact need to close this recordset just like any "regular" recordset. I just need to figured where to run the code from?

What do you Think?

 

[genomon]

Without seeing the error I can only guess that the rs has gone out of scope and you are trying to set "nothing" to nothing. One way to avoid the error would be

IF NOT rs IS NOTHING THEN.....

As far as where to put the code, one good place would be right after you are done with the recordset. Another would be in the Form_Close() event.

Always remember that you're unique. Just like everyone else.

 

[bradmaunsell]

The suggested code returns an error "cannot rin when closed...." in both the CLOSE and UNLOAD event.

However, adding an on error line makes it work fiine in the UNLOAD event.

On Error GoTo ErrorHandler
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
ErrorHandler:
Err = 0

So, the bottom line is that the recordset needs to closed - no different because it was created by running a stored procedure vs. a "standard MDB" Access application.

Thanks for the help!
Brad

 

[bradmaunsell]

PS genomon

Thanks again for the help. I was going to click a formum STAR but can't find where to do that.

No wonder I need help!

Brad

 

[genomon]

Glad it worked! Stars are in the lower left corner of posts, under the sig ("Thank xxx for this valuable post").
Forum rules forbid me to solicit them, but I think it's OK to show you where they are



Always remember that you're unique. Just like everyone else.