POSSIBLE to close all active ADO connection

[sal21]

I use many ado connection in my Excel VBA project.
I need to be sure to close and set nothing all active ADO connection.?
Possible?

[HansV]

There is no built-in collection of all active connections, so unless you create such a collection yourself, there is no way to loop through all connections.

See How to Close All Active Connections of ADO?

[sal21]

There is no built-in collection of all active connections, so unless you create such a collection yourself, there is no way to loop through all connections.

See How to Close All Active Connections of ADO?

Tks Hans, but no effect!

Code: Select all

Sub CHECK_CONNECTION()

Dim m_colConns As Collection
'...Somewhere in your code
'm_colConns.Add cnMyConnection
'...Now in your unload event
If Not m_colConns Is Nothing Then
Dim cn 'you want a variant here
For Each cn In m_colConns
cn.Close
Set cn = Nothing
Next cn

End If
End Sub

[HansV]

You have to use the line

m_colConns.Add cnMyConnection

each time you open a connection cnMyConnection. For example:

Dim m_colConns As Collection
Dim CONN1 As ADODB.Connection
Dim CONN2 As ADODB.Connection
...
Set m_colConns = New Collection
...
Set CONN1 = New ADODB.Connection
CONN1.Open ...
m_colConns.Add CONN1
...
Set CONN2 = New ADODB.Connection
CONN2.Open ...
m_colConns.Add CONN2

Then, at the end of your code:

Dim cn As Variant
For Each cn In m_colConns
cn.Close
Set cn = Nothing
Next cn