"arno" wrote ...

In queries I can link tables form different
sources (a kind of mini-datawarehouse), so
Northwind does not help

Are you saying you cannot (easily) recreate the situation using only
northwind? If failing to clean up was the sole cause of the problem
then you should be able to recreate it using a single data source.

Just to be clear, are you saying that

Sub Test()
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.Open <snip
<snip
' Just let the open connection go out of scope
End Sub

causes the Excel.exe to grow and slow down queries, whereas:

Sub Test()
Dim Con As ADODB.Connection
Set Con = New ADODB.Connection
Con.Open <snip
<snip
Con.ActiveConnection = Nothing
Set Con = Nothing
End Sub

does not?

I run queries vs. not saved excel-files

A not saved Excel file can only mean an open worksheet and the title

BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO

means you are almost certainly going to be experiencing problems due
to the memory leak bug, even if you do explicitly clean up your ADO
objects. That's why I asked for an example that just uses northwind
i.e. it eliminates other elements which may be the cause of the
problem. In lieu of a test, the situation you describe makes me wonder
whether you have misdiagnosed the problem i.e. it could be due to the
Excel memory leak bug, not allowing enough time for connections to
close, Jet failing to clear lock files, etc.

Based on what you originally said:

run queries eg. in a loop and never cleanup. each query
will take longer and longer and the memory usage of
excel (task manager) will grow and grow.

here's my quick test. In a loop, open a connection to northwind, open
an recordset, let the (open) connection and (open) recordset objects
go out of scope, wait a generous three seconds (to eliminate the
possibility of timing issues):

Option Explicit

Private Declare Sub Sleep _
Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Sub Test()

Dim lngCounter As Long

For lngCounter = 0 To 20
GetData
Sleep 3000
Next

End Sub

Function GetData() As Long

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strSql As String

Const strPATH As String = "" & _
"C:\Program Files\" & _
"Microsoft Visual Studio\VB98\" & _
"NWIND.mdb"

Set oConn = New ADODB.Connection

With oConn
.CursorLocation = adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPATH
.Open
End With

strSql = "SELECT lastname FROM employees"

Set oRs = oConn.Execute(strSql)
GetData = oRs.RecordCount

End Function

While this code is running, I look at Excel.exe in task Manager and
see it fluctuating slightly i.e. no significant or constant increase.

I agree that best advice is to explicitly close connections and
recordsets, and I always do so myself. What I'm seeking is evidence
that not doing so actually causes problems.

Thanks,
Jamie.

--