How to return a recordset

[WBH1138]

HI

I have added some code to a class module to get an ADO recordset which I want to return to the calling module.

After this line of code...

Set GetContactNameRecordset = rsContactNames

...both recordsets are populated but as soon as I close RSContactNames, GetContactNameRecordset also closes.

Should I be doing something else?

I've looked at making a 'clone' but is it that over the top for what I want?

Thanks in advance

 

[gmmastros]

Unless I am misunderstanding your question, I think you need to disconnect your recordset.

This is how I do it.

Public Function GetRecordset(ByVal SQL As String) As ADODB.Recordset
    
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set DB = New ADODB.Connection
    
    DB.ConnectionString = GlobalConnectionString
    DB.CursorLocation = adUseClient
    DB.Open
    
    Set RS = New ADODB.Recordset
    RS.CursorLocation = adUseClient
    Call RS.Open(SQL, DB, adOpenStatic, adLockBatchOptimistic)

    Set RS.ActiveConnection = Nothing
    Set GetRecordset = RS

    Set RS = Nothing
    DB.Close
    Set DB = Nothing
    
End Function

Then, to call the function...

Private Sub Command1_Click()

    Dim RS As ADODB.Recordset
    
    Set RS = GetRecordset("Select * From YourFavoriteTable")

    ' Use the recordset here

    RS.Close
    Set RS = Nothing

End Sub

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SBerthold]

So, why do you want to close RSContactNames?

Anyways, you can do this:

Set GetContactNameRecordset = rsContactNames.Clone
rsContactNames.Close


Set the ADO help file (ADO270.CHM) on using clones, and if both are not closed, when/how they stay in sync and when not, and concerning when one is updated, changes reflected in the other.

 

[dilettante]

Just don't close it.

By returning a reference you are "handing off" the object to the caller. Just set the internal reference variable in your Class to Nothing after setting the return value.

 

[WBH1138]

So, why do you want to close RSContactNames?"

I was trying to match the code to our existing wy of doing things which means setting up classes to hold all the dat to be used by forms.

We also have routines to handle the connection and set up of database calls. I'm told the returned recordsets are disconnected but I'm not entirely sure.

Have decided to put this particular call on the form rather than the class to keep the memory usage as low as possible.

Thanks for all your help anyway.