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?
Bookmarks