We help IT Professionals succeed at work.

Access 2007 - Lifetime of an ADODB recordset

SheahaST asked
Medium Priority
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

Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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

Yes, there's no way around that.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.