We help IT Professionals succeed at work.

Access 2007 - Lifetime of an ADODB recordset

SheahaST
SheahaST asked
on
Medium Priority
392 Views
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
    
GetFollowupsRecordSet_Click:
    CloseConnection
    Exit Function
        
ErrorHandler:
    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
        g_adoCon.Close
        Set g_adoCmd = Nothing
    End If
 
End Sub

Open in new window

Comment
Watch Question

Commented:
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

Author

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

Commented:
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.

OR

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.