Difficulty returning ADODB recordset from function

[randman1]

I'm trying to return a ADO recordset from a function that can be iterated, have records counted, etc. I can return the recordset if I do not close either the recordset or connection but know this to be bad practice as well as causing the MDB file to bloat. I've seen some solutions on the Net but none of them applied to Access VBA. An over simplified version of my code is as follows.

Public Function QueryServer(ByVal sSql as String) As ADODB.Recordset
    Dim cn as New ADODB.Connection
    Dim rs as New ADODB.Recordset

    cn.CursorLocation = adUseClient
    rs.Cursor = adUseClient

    cn.Open = "File Name=MyUDLFilePath"
    rs.Open sSql, cn, adOpenStatic

    Set QueryServer = rs
    cn.Close

End Function


Private Sub Command0_Click
    Dim sSql as String
    Dim rs as ADODB.Recordset

    sSql = "SELECT * FROM SomeTable"
    Set rs = QueryServer(sSql)

    Do While Not rs.EOF
        Debug.Print rs.Fields(0).Value
    Loop

    rs.Close

End Sub

[ssanfu]

What is the advantage of using a function to open a record set, rather than opening the record set in the sub??? I would add the code necessary to open the record set to the sub.

[randman1]

The primary advantage is that the function that returns the recordset does all of the error handling and is contained in a class that already has the other information necessary for connections. If the function was a simple as the above example, then I would agree that opening the recordset locally would be the thing to do.

[ssanfu]

Interesting... I'll have to look into this. I use DAO, so opening a record set doesn't take a lot of code. But it is something to look into.

Back to your problem, you need to close the record set, close the connection, set the record set to nothing and set the connection variable to nothing. Not sure how (timing) to do this when the record set is opened as a call to a function. Maybe someone else will have some ideas.

[orange]

I did a quick google and found these that might apply as steve said.
http://forums.devx.com/archive/index.php/t-46549.html
http://p2p.wrox.com/vb-databases-bas...recordset.html