How to pass disconnected recordset to variable to close original

[bradyboyy88]

I was wondering how do you actually transfer one disconnected recordset to another so that you can close the original. I have a function below that passes the disconnected recordset as a function but if i were to put oRs.close which is the original recordset than the variable I just passed to is closed also. So how do I truly copy one recordset to a new variable so I can get rid of the first one which is used in my function. Also, if I do not close the original recordset object isnt it still in memory hence a memory leak?

Option Explicit
'Database connection variables
Public oConn As Object
Public oRs As Object
Public sConn As String


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
                                 
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockPessimistic


RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub'Return a query as a recordsetPublic Function SQLQueryDatabaseRecordset(SQLQuery As String) As Variant           'Open Record Set by executing SQL    oRs.Open SQLQuery, oConn        'Disconnect the recordset    Set oRs.ActiveConnection = Nothing            'Return recordset    Set SQLQueryDatabaseRecordset = oRs    End FunctionPublic Sub SQLCloseDatabaseConnection()    'Close Connection    oConn.Close        'Clear Memory    Set oConn = Nothing    Set oRs = Nothing

 

[RoryA]

You only have one recordset so you can't close it and expect it to be open in another variable pointing to it.

I don't really understand why you have oRs as a public variable as well as using it as the result of a function.

 

[bradyboyy88]

I guess everything did not paste correctly. The reason i put oRs as public variable is because I have split my functions and subroutines into the following parts:
Subroutine to create ADO connection object and recordset object and then open connection
Function to run query and return disconnected recordset as function and sets original recordset variable to nothing
Subroutine to close ADO connection object and set to nothing

This is saved in a class module. I split it so I could keep the connection open and not have to keep recreate those objects (I figure its a performance loss) until I am done and run a batch of queries which would return as disconnected recordsets to save in variables in the main program. I am worried that since I never get to close oRs since its a disconnected recordset then it is going to stay in memory.

Option Explicit
'Database connection variables
Public oConn As Object
Public oRs As Object
Public sConn As String


'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As Variant
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
    Set oRs = Nothing
    
End Function


Public Sub SQLWriteDatabase(SQLQuery As String)
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn


End Sub


Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
                                 
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
    oConn.CursorLocation = adUseClient
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub




Public Sub SQLCloseDatabaseConnection()


    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing


End Sub

 

[RoryA]

It will only stay in memory as long as there is a reference to it being held somewhere. Setting oRs to Nothing is all that your code needs to do.

I don't really understand why you have structured your code the way you have though. I can't see any benefit to keeping a Recordset variable at module level rather than just creating it when you need it. You also don't need one to write to a database - you can use the Connection for that.

 

[bradyboyy88]

In my mind it seems easier to manage everything by splitting up my parts of the ADO process and to do that requires public variables or passing as arguements and I just went with the public variables. Maybe bad practice but at the time it made sense lol. I also thought having a single function to manage creating my recordsets and then assigning them to permanant variables for use later on despite other queries and new recordsets might need to be made.

Let me pose a few examples and if you can tell me the outcome I think it will clear some stuff up for me

So if setting the variable oRs to nothing then after this code we should have only 1 recordset open?

Sub Test()

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "Some Query"
Set oRs=Nothing

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "Some Query"

End Sub

If the original recordset is set equal to another variable (oRs2) but then set to nothing, does the recordset for the new variable oRs still exist in memory since the previous recordset was pointed to it?

Sub Test()

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "Some Query"
Set oRs2=oRs
Set oRs=Nothing

End Sub

Last example, re using the same logic but twice, how many recordsets are left in memory or open after this?

Sub Test()

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "Some Query"
Set oRs2=oRs
Set oRs=Nothing

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "SomeOther Query"
Set oRs2=oRs
Set oRs=Nothing

Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "SomeOther Query"
Set oRs3=oRs
Set oRs=Nothing
End Sub

I guess my confusion comes from the fact that I have read various articles about objects needing to be closed and then set to nothing for it to be truly clear from memory. I just do not understand why setting the recordset to nothing bypasses the need to close also. This fact alone has made my function work but I just dont understand why and how it doesnt create a memory leak .

Thanks again for your help!

 

[RoryA]

I'll try and keep this short as I'm on a phone.

General good practice is that if you open something you should close it. Some will say that this is as unnecessary as setting objects to Nothing but with ADO in the past that has been a memory issue too. I've never looked into the memory usage to determine if this is still a factor as it's easier to just close them when done.

Not explicitly closing record sets and connections can lead to connection creep as resources aren't released back into the pool even though setting to Nothing should have the same effect in theory. Resource pooling is a good thing so best to make sure you're taking advantage of it.

If you open a recordset using a variable and then assign it to another variable, there is only one recordset (as with any object) so if you explicitly close the first variable, you're closing all of them.

 

[bradyboyy88]

So I have a loop running and keep creating ado connections and set to nothing. If the loop keeps going shouldnt I see in the memory in the processes tab of task manager increase for excel if its a memory leak?

 

[RoryA]

Yes, you would, if it's a memory leak.

 

[bradyboyy88]

Well I let the loop run for about an hour and a half and did not see any difference in memory usage!