Using ADO 2.8, I have been attempting to write a function which, when passed a string containing an SQL statement and a DSN to executes the SQL against the database at that address, and returns a disconnected recordset object. This is my first try:

Function GetRS(strSQL, strDSN)
'this function returns a disconnected RS

'Declare variables
Dim Cnn
Dim RS

'Open connection
Set Cnn = New ADODB.Connection
Cnn.Open strDSN

'Create the Recordset object
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient

'Populate the Recordset object with a SQL query
RS.Open strSQL, Cnn, adOpenStatic, adLockBatchOptimistic

'Disconnect the Recordset
Set RS.ActiveConnection = Nothing

'Return the Recordset
Set GetRS = RS

'Close everything
Cnn.Close
RS.Close
Set Cnn = Nothing
Set RS = Nothing
End Function

The function does return a recordset, but when rs is closed, the recordset stored as GetRS is also closed, so the function retuns a closed, and hence useless recordset object.

Any ideas on how to prevent this?