ADO Connection and Recordset Paradigm

[deadseasquirrels]

I am very new to this whole ADO thing as well as to DB programming in general. One thing I'm confused about is the whole concept of a recordset. I thought a recordset was a like a returned array (for lack of a better analogy), which is basically a representation of a table.

But one thing I'm trying to do tells me otherwise. I'm trying to write a function that returns a static recordset and then closes the recordset and closes the database connection. However that isn't possible, because once those things are closed the recordset that I returned is closed. So it looks like i can't pass back a static version of a recordset. Fine. Is this true, or is there a way to return a static recordset and close the original.

Second. Another thing I tried was to close the database connection and then return the recordset. When I did that, the recordset was still there oddly enough. It's like unpluggin the TV and leaving the cable in and the TV still works. Can somebody explain this weird paradigm to me? Thank you.

[malleyo]

I'm trying to write a function that returns a static recordset and then closes the recordset and closes the database connection. However that isn't possible, because once those things are closed the recordset that I returned is closed. So it looks like i can't pass back a static version of a recordset. Fine. Is this true, or is there a way to return a static recordset and close the original.

Do a search for disconnected recordset. This will allow you to query the database, close the connection, and still be able to read the recordset. Here is function I use:

Dim rsData As ADODB.Recordset

Set rsData = OpenRecordset("SELECT * FROM TableName")



Public Function OpenRecordset(ByVal strQuery As String) As ADODB.Recordset
    
    
    On Error GoTo ErrHandler
     
    Dim rsData          As ADODB.Recordset
    Dim cmdData         As ADODB.Command
    
    Set rsData = New ADODB.Recordset
    Set cmdData = New ADODB.Command
       
    'Let the Command set the ActiveConnection
    Set cmdData.ActiveConnection = cnConnection
    cmdData.CommandText = strQuery
    cmdData.CommandType = adCmdText
     
    'CursorType = adOpenDynamic     <-- Required for RecordCount Property to work Properly
    'CursorLocation = adUseClient   <-- Required for RecordCount Property to work Properly
    '        CursorLocation = adUseClient is necessary for the disconnected recordset
    rsData.CursorType = adOpenDynamic
    rsData.CursorLocation = adUseClient
    
    rsData.Open cmdData
    
    'Set the Recordset ActiveConnection to Nothing
    Set rsData.ActiveConnection = Nothing
 
    Set OpenRecordset = rsData
        
    GoTo ExitFunction

ErrHandler:
    MsgBox "Error: " & Err.Number & " - " & Err.Description & vbCrLf & "Occurred in OpenRecordset"
    Set OpenRecordset = Nothing
    
ExitFunction:
    Set cmdData = Nothing
    
End Function

[deadseasquirrels]

I looked up openRecordset and that is a predefined function, even though it looks like in VBA it lets you overwrite that function.

I forgot to mention that I am actually writing VBA code and not VB, I'm not so sure that VBA even lets you create disconnected recordsets...but your function does look like it works.

[malleyo]

Just change the name of the function to OpenADORecordset or something else. It can be called whatever you want.

[deadseasquirrels]

Yeah, I know, I was just wondering if you were pointing me to that function, because iI think you can use that built in function to create a recordset that is static called like 'snapshot' or something. I guess it was just coincidence then that you used that function name.

[malleyo]

I didn't know about the built in function. I also don't use VBA. It might just be a VBA thing?