Correct way to close an ADO recordset in Access when passing to a call method?

Question

I am using Access 2003 to access data stored in a SQL Server database through ADO. Most often, I get data using stored procedures. I execute stored procedures through ADO in a separate function that returns a set of records. When you return a recordset from a function, where is the best way to close a recordset and free up memory? Is this executed in a function that returns a set of records, or is it executed in a sub / function function that calls a function that executes ADO code? Here is an example ...

Here is the calling method

Public Sub CallingMethod()
  Dim rs As ADODB.Recordset
  Set rs = GetDataFromDatabase()
  Set myListBox.Recordset = rs
  rs.Close
  Set rs = Nothing
End Sub

Here is a method that actually executes the stored procedure and returns it to the calling method

Public Function GetDataFromDatabase()
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set cnn = New ADODB.Connection
  cnn.ConnectionString = myConnectionString
  cnn.Open

  Set rs = New ADODB.Recordset
  Set rs.ActiveConnection = cnn
  rs.Source = "EXEC uspMyStoredProcedure"

  rs.LockType = adLockOptimistic
  rs.CursorType = adOpenStatic
  rs.CursorLocation = adUseClient
  rs.Open

  Set GetDataFromDatabase = rs

  Set rs = Nothing
  Set cnn = Nothing

End Function

, . ? . .

Answer 1

, . , . . , , , .

Public Sub test_GetDataFromSP()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "DRIVER=SQL Server;SERVER=VM2003\SQLEXPRESS;" & _
        "Trusted_Connection=Yes;DATABASE=Inventory"
    cnn.Open
    Set rs = GetDataFromSP("GetCenterCodes", cnn, "14, 14, 501")
    rs.MoveLast
    Debug.Print rs.RecordCount
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Public Function GetDataFromSP(ByVal pProc As String, _
    ByRef pConnection As ADODB.Connection, _
    Optional ByVal pArguments As String) As ADODB.Recordset

    Dim rs As ADODB.Recordset
    Dim strStatement As String

    strStatement = "EXEC " & pProc
    If Len(pArguments) > 0 Then
        strStatement = strStatement & " " & pArguments
    End If
    strStatement = strStatement & ";"
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = pConnection
    rs.Source = strStatement
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenStatic
    rs.CursorLocation = adUseClient
    rs.Open
    Set GetDataFromSP = rs
End Function