Is there a way to loop through all open ADO recordsets to close them without having to know which are open and what their names are?
I know you could do this with DAO, but I have to use ADO for this project I'm working on and I would like to come up with a way to ensure that I close all open recordsets and then set them all to nothing.
Whether writing in DAO or ADO, I always close and set to nothing all recordsets from within the sub or function where they are created, so I've never needed to do this. How do you do this in DAO?
I do too, but as this project contains a lot of code, I was hoping to be able to find something to make sure that I did release all of the ADO recordsets.
'--------------------------------------------------
Public Function CloseAllRecordsets() As Integer
'--------------------------------------------------
'Be sure to Close all Data Objects upon Exit - and release memory
'Put it on close of the Main Menu - (make sure you have no bound forms open)
Dim wsCurr As Workspace
Dim dbCurr As Database
Dim Rs As Recordset
Dim Frm As Form
For Each wsCurr In Workspaces
For Each dbCurr In wsCurr.Databases
For Each Rs In dbCurr.Recordsets
MsgBox "The recordset " & vbCrLf & Rs.Name & vbCrLf &
Rs.RecordCount & " record(s) was left open - now closing it.", vbCritical,
"Validation"
Rs.Close
Set Rs = Nothing
Next
The short answer is: no, there is not a way to do this for ADO.
The DAO method works because if a statement like the following is executed:
Code:
Set Rs = dbCurr.OpenRecordset("SELECT * FROM MyTable")
The VBA environment:
* creates a new Recordset object
* adds it to the dbCurr.Recordsets collection
* sets Rs as a ByRef object pointer to that recordset
From that point, calls to Rs would be the same as calling the Recordset object itself. For example, if Rs refers to the first recordset in dbCurr:
Code:
Debug.Print Rs.RecordCount
...is the same as
Code:
Debug.Print dbCurr.RecordSets(0).RecordCount
So, when you run the CloseAllRecordsets function, you are not closing the recordset variables themselves; you are actually closing the Recordsets in the dbCurr.Recordsets collection.
HOWEVER:
With ADO, there is no Recordsets collection in the ADODB.Connection object model. In the following code:
Code:
Dim dbCn As ADODB.Connection
Dim Rs As ADODB.Recordset
Set dbCn = CurrentProject.Connection
Set Rs = New ADODB.Connection
Rs.Open "SELECT * FROM MyTable", dbCn
...when the Recordset object is created, it is a freestanding Recordset object, which remains active until the following statements are executed:
Code:
Rs.Close
Set Rs = Nothing
If this Recordset object is not closed and set to nothing before the program exits, there is no reference in the ADODB.Connection object model for the calling program to tell if there are any open ADODB.Recordsets using it. All you can tell the program to do, at that point, is to close the ADODB.Connection object and set it to Nothing.
A workaround would be to create a class module that basically represents the collection of ADODB recordsets, and call the object from that class module instead of the ADODB.recordset, which ensure that we have a collection of all recordset instanced and thus can reference to the collection to destroy all recordsets.
Thanks ByteMyzer, very informative. I've moved to using small single query functions in my new code, it's the old code I'm worried about. I'm fairly positive that when you exit a procedure all local variables (including the recordset) are set to nothing, correct?
Banana, how would I go about using the class idea of yours? I'm more of linear programmer with modules and forms, so this would be a nice lesson for me. Are you suggesting using an array of recordsets I.E: dim rs(15) as adodb.recordset ?
Indeed, local variables do get cleaned up at end of every procedure unless declared as static. I suspect the problem would arise if you need to hold a reference to recordsets between multiple procedures or same procedure at different time, which would use private or public variable, and may not be cleaned up until the end of application's scope (e.g. when we quit Access). So if all you have are local variable referencing the recordsets, I would be more judicious in ensuring that the procedure sets all variable to nothing once done.
As for the class module, the idea is just to create a clone of ADODB.recordset. Here's a prototype:
Code:
Option Compare Database
Option Explicit
Implements ADODB.Recordset '<--- Ensure we have all methods & procedures
'This is just an example
Public Function AbsolutePosition() As Long
AbsolutePosition = ADODB.Recordset
End Function
... 'Repeat for all other methods
...
...
Private Sub Class_Initialize()
col.Add New ADODB.Recordset()
End Sub
Private Sub Class_Terminate()
col.Remove This ADODB.Recordset()
End Sub
We would refer a collection (named col) to add and remove the recordset everytime we instance a object based on that class which is basically a wrapper for the ADODB.Recordset (e.g. all methods just calls the ADODB.recordset's own methods). Some experiment will be required to ascertain whether the collection variable should be stored in the class module or a separate module and how we will refer to the individual ADODB.Recordset.
Of course, there's also a small performance penalty because we've added a layer, so it would only really make sense if you were working with so many recordsets. For a handful, it may be just easier to add to the collection manually without using a class module, something that can be done with a Find tool to ensure there is always a line after the creating the recordset to add it to the collection.
Wow, that seems really complex for my simple brain to comprehend. How would I make sure I have the correct recordset? Or would this essentially keep track of them internally and when I tell it to loop through them and close any open ones, it's going to know which ones are open?
That's the point of the two events, Initalize and Terminate in any class module.
Suppose we create a class module called foo.
As soon as I hit this line:
Code:
Set x=New foo
The initialize event immediately fires and that's where I add the x to the collection. Likewise when I do this:
Code:
Set x=Nothing
The Terminate event fires and I remove it from the collection.
And when you need to flush all the recordsets, you just look to the collection itself and destroy everything in it. So in a sense, it's self-tracking when you create/destroy a recordset, but if you need to flush the lot, it's manual.
If I'm using probably 15 different recordsets in my code, would you recommend using this wrapper or should I stick to making sure that the recordsets are being closed?
The reason I'm so interested in this is that our outdated inventory system is not clearing old connections. So when an ADO recordset is left open, it takes up one of our connections and after 250 connections the system no longer allows new connections and the server has to be rebooted. I'm looking for a way to be sure that once my code is complete there are no recordsets left open.
Yeah, if that is a problem, then you could tinker with the wrapper approach. My other halve of brain wonder if it's possible just to reference the ADODB recordset directly without doing a wrapping call to its individual methods & properties, but that would require experimentation to be 100% sure if it's possible.
One more thing....
I may be possibly just confused, but I think that even if we destroyed all recordsets, isn't the Connection object the real bugbear that eats up the connection, since all recordsets use the connection object?
I may be possibly just confused, but I think that even if we destroyed all recordsets, isn't the Connection object the real bugbear that eats up the connection, since all recordsets use the connection object?
Well from the server point of view it's seeing it as the connection to the table the records returned.
So if I return 1500 records, it has a marker that I have out 1500 records. The biggest problem occurs if I don't set the recordset to read-only, then if someone modifies one of the 1500 records it'll attempt to update mine (I think) and then lock the user out of modifying them.
But when I close the recordset properly, I'm assuming it communicates to the server and then the server closes the connection.
To be quite honest, I don't use a separate ADO connection but use the recordset.activeconnection, perhapse moving to an ADO connection variable and using that globally would be the better option.
Unfortunately, I don't have access to the server side applications to test my theories.
Well, one way to test this theory is to see whether the marker on the server goes away when Access is quit. See, Access is supposed to clean out and close the activeconnection, then it should be sufficient. But if server only look at the recordsets rather than the connection, then it won't matter a whit if it's Access's default connection or a connection object set in a variable.
Well, one way to test this theory is to see whether the marker on the server goes away when Access is quit. See, Access is supposed to clean out and close the activeconnection, then it should be sufficient. But if server only look at the recordsets rather than the connection, then it won't matter a whit if it's Access's default connection or a connection object set in a variable.
I'm 100% positive that Access being closed wont clear the marker from the server. We had a computer that had over 75 markers on the server with the machine being turned off. Deducing that it's a recordset issue.
So, my next project is to look into a wrapper. Thanks for your help Banana, really appreciate it!
Well, if that's in reference to the code I gave to Tanner, looking at it again makes me wonder what the heck I was smoking at that time. It's just complicated for no good reason.
A much better solution would be to write a class module and reference it always for opening/closing recordsets in ADO.
Code:
Option Compare Database
Option Explicit
Private col As New Collection
Private cn As ADODB.Connection
Private rs AS ADODB.Recordset
Property Get CurrentConnection() As ADODB.Connection
Set CurrentConnection = cn
End Property
Property Let CurrentConnection(NewConnection As ADODB.Connection)
If Not cn Is Nothing Then
If Not cn.State = adStateClosed Then
Me.CloseAllRecordsets
cn.Close
End If
End If
Set cn = Nothing
Set cn = NewConnection
End Property
Public Function OpenNewRecordset(...) As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open ...
col.Add rs
Set OpenNewRecordset = rs
End Function
Public Sub CloseAllRecordsets()
Do Until col.Count = 0
col(1).Close
Set col(1) = Nothing
col.Remove 1
Loop
End Sub
(untested air code)
The problem is that it now depends on the programmer (you) but more especially the future developer to consistently use this instead of creating their own ADODB.Recordset, but at least you have a way to know which are open, and clear the collection when needed.
