Access 2007 - Lifetime of an ADODB recordset

Posted on 2009-02-08
Last Modified: 2012-05-06
The following function executes a stored procedure which returns a recordset.
After the "End With" statement the immediate window shows g_rstFindCall.EOF as FALSE.
After the CloseConnection method executes the immediate window considers the g_rstFindCall is Closed, even though there is no explicit code to do this.   How can I extend the lifetime of the record set after the connection to the database has closed?
No where do I do an Set g_rstFindCall = New ADODB.Recordset as I think the
Set g_rstFindCall = .Execute    'retrieve the recordset
statement is doing this.

Thanks in advance

Dim g_rstFindCall As ADODB.Recordset

Private Function GetFollowupsRecordSet() As Boolean

    On Error GoTo ErrorHandler


    Dim prm1 As ADODB.Parameter


    ' Clean up the last record set

    Set g_rstFindCall = Nothing


    If (Not ConnectToSQLServer("GetFollowupsRecordSet")) Then

        GetFollowupsRecordSet = False

        Exit Function

    End If


    With g_adoCmd

        .CommandText = "GetFollowups"

        .CommandType = adCmdStoredProc

        Set prm1 = .CreateParameter("@lAgentID", adInteger, adParamInput)

        .Parameters.Append prm1

        .Parameters("@lAgentID").Value = g_lUserID ' input value

        Set g_rstFindCall = .Execute    'retrieve the recordset

    End With

    GetFollowupsRecordSet = True




    Exit Function



    Dim results

    results = MsgBox("Error encountered while getting followup information!" & vbCrLf _

                     & "Unexpected error - Error #" & Err.Number & " Description: " & Err.Description & " in method 'GetFollowupsRecordSet'", vbCritical)

    GetFollowupsRecordSet = False

    Resume GetFollowupsRecordSet_Click

End Function

Public Sub CloseConnection()

    ' Close the connection that was opened in ConnectToSQLServer

    If (g_adoCon.State = adStateOpen) Then


        Set g_adoCmd = Nothing

    End If

End Sub

Open in new window

Question by:SheahaST
    LVL 16

    Accepted Solution

    Use .getrows to copy the contents of your recordset to an array before you close it.  

    Author Comment

    Thanks,  I wasn't familar with .getrows.   So closing the connection to the database implicitly closes the recordset?
    LVL 16

    Expert Comment

    Yes, there's no way around that.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    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…

    761 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

    15 Experts available now in Live!

    Get 1:1 Help Now