Closing all open recordsets in ADO

[dluby]

Hi, is there a way to loop through all open recordsets in a particular connection (For Each RS In ...) and close them? I know this can be done using Workspaces, but I don't use workspaces. An example of how I make a connection is below:

Dim cnDB as New ADODB.Connection
Dim rsDB As New ADODB.Recordset

cnDB.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TestDB.mdb"
cnDB.Open
rsDB.Open sSQL$, cnDB, adOpenDynamic, adLockReadOnly

Tks

Damian

 

[zemp]

There is no recordset collection in ADO that would allow that. However if you close the connection object you will close all active recordset objects that are associated with the connection.

zemp

 

[zemp]

One more thing to beware of, closing a Connection object while there are open Recordset will roll back any pending changes in all of the Recordsets. Closing a Connection object while a transaction is in progress will generate an error.

Also, if a Connection object falls out of scope while a transaction is in progress, the transaction is rolled back.



zemp

 

[mattKnight]

many people, myself included, don't use

Dim cnDB as [b]New[/b] ADODB.Connection
Dim rsDB As [b]New[/b] ADODB.Recordset

as it can lead to objects reinstancing themselves. It is also less effecient (slightly)

try

Dim cnDB as ADODB.Connection
Dim rsDB As ADODB.Recordset

Set cnDB = New ADODB.Connection
Set rsDB = New ADODB.Recordset

instead

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[EBGreen]

If I had the need to do this, then I would try adding each recordset to a dictionary as I opened them. I've never done this so I don't know for fact that it is possible, but I think it should be in theory.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]