arno said:
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:
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.
--
