filter with vb code

[Guest]

Please forgive my lack of brain functions today.

This may sound silly, but I’ve been working with access for weeks and cant
wrap my brain around this problem in excel.

It’s rather straight forward.
I have a large table some 19000 records.
Each record has 18 columns.
I want to select all records with a value from column # 12 that is equal to
variable “A1â€.
Then take all matching records and copy to a new workbook and save.
Then do again with different value.

Dan

 

[Guest]

Hello Dan,

Speaking of Access, using ADO might make some sense with such a quandary.
Here's an example that creates a new workbook in c:\temp\ named Book1.xls:

Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the
variable is located in the 2nd Sheet, A1. The workbook that your running this
from must be saved so that a proper connection can be established.

More info can be found on such an approach here:
http://support.microsoft.com/kb/295646/EN-US/

Regards,
Nate Oliver

 

[Guest]

I need to keep this in excel if I can.

Any ideas?
Let me know.

Dan

 

[Tom Ogilvy]

You can filter data to a new location with Data=>Filter=>Advanced filter.
I assume A1 was representative. If you need code, turn on the macro
recorder while you do it manually - then turn off the recorder. This
records a single command which should be easy to modify to take variable
arguments and place into a loop.

--
Regards,
Tom Ogilvy

I need to keep this in excel if I can.

Any ideas?
Let me know.

Dan

Hello Dan,

Speaking of Access, using ADO might make some sense with such a quandary.
Here's an example that creates a new workbook in c:\temp\ named Book1.xls:

Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the
variable is located in the 2nd Sheet, A1. The workbook that your running this
from must be saved so that a proper connection can be established.

More info can be found on such an approach here:
http://support.microsoft.com/kb/295646/EN-US/

Regards,
Nate Oliver

 

[Jamie Collins]

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"

More info on why this is a bad idea can be found here:

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

Jamie.

--

 

[Guest]

I need to keep this in excel if I can.

Right, this is all in Excel. Excel can act as a Jet DB as well. I wrote the
code in Excel for Excel and even tested it. One potential issue is that your
data table would really need to look like a DB.

Tom's suggestion is more straightforward although I'm not sure about
looping. You could pass the copied, filtered data to a new workbook, save and
close.

Bon chance,
Nate Oliver

 

[Guest]

Interesting,

Note they're speaking to querying an open Excel file. One of the benefits of
using ADO to query a spreadsheet is that the file can (and apparently should)
be closed.

Not to mention that ADO is extremely fast.

Cheers,
Nate

 

[Tom Ogilvy]

Looping would loop through a list of unique items (also can be built with
advanced filter) and would put each of the items in this list as criteria in
the criteria cell/range, adjust the destination (which would be a variable
in the advanced filter command), then execute the filter. Perform any
remaining administration.

I have successfully used this many times as I am sure you have yourself. So
apparently my suggestion was not clear or was vague as to what I meant.
Thanks for pointing that out.

 

[Guest]

Tom, I apologize, I missed the following in the original post:

Then do again with different value.

Which is exactly where looping comes into play as you have mentioned. Thank
you for following up.

Regards,
Nate Oliver

 

[Jamie Collins]

Note they're speaking to querying an open Excel file.

*You* were suggesting to the OP to query an open Excel file by using
ThisWorkbook.FullName in the connection string.

Jamie.

--

 

[Guest]

Hello Jamie,

Where's the ADO Recordset in my post?

Quote by MS:
"When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an
Excel worksheet that is open in Excel, a memory leak occurs in the Excel
process. Repeated queries may eventually cause Excel to run out of memory and
raise an error, or cause Excel to stop responding."

Workaround by MS:
"Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel
data to a new worksheet. For additional information about using the SELECT
INTO syntax to export data, click the following article number to view the
article in the Microsoft Knowledge Base:
295646 How To Transfer Data from ADO Data Source to Excel with ADO"

My Original Post:
Sub ExToEx()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
cn.Execute "SELECT Col1, Col2 INTO [Excel
8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
cn.Close: Set cn = Nothing
End Sub

Look familiar?

Regards,
Nate Oliver

 

[Jamie Collins]

Where's the ADO Recordset in my post?

Quote by MS:
"When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an
Excel worksheet that is open in Excel, a memory leak occurs in the Excel
process.

Workaround by MS:
"Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel
data to a new worksheet.

Nate, It's not your fault but what can I do? I've tried giving
Microsoft 'feedback' about this article but they have so far taken no
notice; I guess ADO classic doesn't interest them. So my only recourse
is to spread the word myself and jump on anyone in the ngs who repeats
MS's bad advice.

There may be no recordset in your post be there certainly is a memory
leak.

I've done extensive testing using the very same performance monitoring
code found in the article in question and I think I know the truth. The
key to the issue is not the recordset, not the connection, not the
provider or driver. It's as simple as this: using ADO to execute Jet
SQL involving an open workbook causes a memory leak.

Let's be fair: did you test your assertion or did you just put your
faith in Microsoft? I did a test just now. My connection was to a .mdb
MS Access/Jet database i.e. not even an Excel connection. I executed
the following SELECT..INTO..FROM which does not return a row set and I
certainly did not use a recordset:

SELECT *
INTO [Excel 8.0;Database=C:\db1.xls;].Sheet1
FROM [Excel 8.0;Database=C:\db.xls;].[Sheet1$];

With the source workbook open I got a memory leak. When it was closed,
I experienced no leak.

I'm not sure how I can prove to you that the leak occurs other than to
say, try it for yourself. If you don't have VB6 or other requirements
of the code, just open Windows Task Manager, switch to the Performance
tab and keep you eye on the Mem usage: if the value increases but does
not return to something very close its original value then you're
leaking.

If any of the regulars who use ADO (you know who you are <g>) and have
VB6.0 are willing to test and post their experience/results here, I
would be grateful. Here is the link to the article and code:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

I really wish I was wrong on this one: it would be nice to be able to
safely operate on an open workbook. There must be someone out there
itching to publicly show me up ...?

Jamie.

--

 

[Guest]

Nate, It's not your fault but what can I do? I've tried giving

Microsoft 'feedback' about this article but they have so far taken no
notice; I guess ADO classic doesn't interest them. So my only recourse
is to spread the word myself and jump on anyone in the ngs who repeats
MS's bad advice.

How noble. Don't hold your breathe while you wait for them to fix that, I'm
under the impression that they've moved on. I've spoken directly with
Microsoft personnel regarding incorrect KBs, e.g.,

http://www.utteraccess.com/forums/showflat.php?Number=380493&fpart=2.2

And, nothing.

There may be no recordset in your post be there certainly is a memory
leak.

Pity, I'm going to have to agree. It ruins something so simple and fast like
the following:
http://www.xtremevbtalk.com/showpost.php?p=864947

Let's be fair: did you test your assertion or did you just put your
faith in Microsoft?

Have to admit, I did take Microsoft's word for it on this one, it is their
product.

I'm not sure how I can prove to you that the leak occurs other than to
say, try it for yourself. If you don't have VB6 or other requirements
of the code, just open Windows Task Manager, switch to the Performance
tab and keep you eye on the Mem usage: if the value increases but does
not return to something very close its original value then you're
leaking.

Don't worry about it, you don't need Visual Studio to see it. What I did
notice is that the leak doesn't compound as you recall the procedure, it
appears to stabilize after the first leak.

I really wish I was wrong on this one: it would be nice to be able to
safely operate on an open workbook. There must be someone out there
itching to publicly show me up ...?

I wish you were wrong too. I guess if you're going to use ADO with Excel,
you need to to either kill the App when you're done, if you can live with a
temporary leak or separate the code and data, which sounds familiar.

See you around.

Regards,
Nate Oliver

 

[Guest]

I'm not sure how I can prove to you that the leak occurs other than to

say, try it for yourself. If you don't have VB6 or other requirements
of the code, just open Windows Task Manager, switch to the Performance
tab and keep you eye on the Mem usage: if the value increases but does
not return to something very close its original value then you're
leaking.

How unfortunate, perhaps I was too quick to agree with you, Jamie. Memory
consumption doesn't have to be a leak.

The leveling off of the memory consumption versus continual degredation
leads me to believe that Microsoft hasn't changed their kb to your point of
view because it would be incorrect. What we're probably looking at is a Jet
cache in memory, which is refreshed on subsequent calls (hence the stability
following call #1).

See [Microsoft's] Russ Gray's post here:

http://groups-beta.google.com/group/microsoft.public.data.ado/msg/3e0021430755ed35

And the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q247140

Nate, It's not your fault but what can I do? I've tried giving
Microsoft 'feedback' about this article but they have so far taken no
notice; I guess ADO classic doesn't interest them. So my only recourse
is to spread the word myself and jump on anyone in the ngs who repeats
MS's bad advice.

Well, someone recently told me that the proliferation of myths flies in the
face of 'the Usenet way', so I might advise against posting as such.

Cheers,
Nate Oliver

 

[Jamie Collins]

The leveling off of the memory consumption versus continual degredation
leads me to believe that Microsoft hasn't changed their kb to your point of
view because it would be incorrect. What we're probably looking at is a Jet
cache in memory, which is refreshed on subsequent calls (hence the stability
following call #1).

See [Microsoft's] Russ Gray's post here:

http://groups-beta.google.com/group/microsoft.public.data.ado/msg/3e0021430755ed35

And the following:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q247140

Nate, Where's that healthy (or otherwise) cynicism of yours from the
other thread? You distrust their VB garbage collector based on no
evidence (design faults in DAO, Excel and other components cannot be
attributed to the garbage collector), and no testing or observations of
your own. Yet in this thread you observe the memory leak for yourself
but then you go running back to Microsoft to try to explain it away.

Think about it, I mean, think about it *yourself*: if the Jet cache is
the explanation, why does usage climb when the workbook is open but
remain stable when the workbook is closed? You may be seeing some
stabilization/recovery but do you get (close to) everything back when
done?

I changed my test (I'll post the exact code and output at the end) to
do 50 iterations, the first 10 with the workbook open, the next 10 with
it closed, the next 10 with it open etc. As before I was using a
connection to an mdb jet database. I closed and released the ADO
Connection on each iteration.

As expected, with the workbook open I saw the usage steadily increase
on each of the first 10 iterations. For iteration 11, the first with
the workbook closed, there was a recovery but at a level much increased
from the original amount used by Excel. For iterations 11 to 20 with
the workbook closed the usage was *exactly* the same each time. For
iteration 21, with the workbook open again, there was an immediate and
significant rise in usage and it kept rising for the next nine
iterations. This pattern continued to the end.

I immediately ran the code again and the usage hadn't recovered much
from the last iteration i.e. even closing and releasing the Excel
application didn't get the memory back.

This looks exactly like a memory leak to me i.e. I have closed and
released everything and AFAIK flushed the Jet cache in the process but
I haven't got the memory back. If not a memory leak then what is it?
And how to I get this non-leaking yet absent memory back?

someone recently told me that the proliferation of myths flies in the

face of 'the Usenet way', so I might advise against posting as such.

Actually, I told you can expect people to jump on your case if your
sole piece of 'advice' is based on superstition rather than fact.

I may have made a mistake somewhere but I'm making my assertions in
good faith based on my observations. I am not trying to be contrary for
the sake of it. I am open-minded to being wrong and, as I said before,
I hope I am wrong. I've posted my code and my output (below). In what
way do you think I am trying to invent or repeat mythology?

Here is my output in full:

Memory used by Excel: 2166784 <<< original
Memory used by Excel: 9183232 <<< iteration 1: workbook open
Memory used by Excel: 9490432
Memory used by Excel: 9814016
Memory used by Excel: 10125312
Memory used by Excel: 10440704
Memory used by Excel: 10760192
Memory used by Excel: 11083776
Memory used by Excel: 11407360
Memory used by Excel: 11710464
Memory used by Excel: 12013568
Memory used by Excel: 8048640 <<< iteration 11: workbook closed
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 8048640
Memory used by Excel: 12644352
Memory used by Excel: 12939264
Memory used by Excel: 13238272
Memory used by Excel: 13541376
Memory used by Excel: 13844480
Memory used by Excel: 14151680
Memory used by Excel: 14450688
Memory used by Excel: 14757888
Memory used by Excel: 15065088
Memory used by Excel: 15372288
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 11395072
Memory used by Excel: 15953920
Memory used by Excel: 16236544
Memory used by Excel: 16535552
Memory used by Excel: 16838656
Memory used by Excel: 17145856
Memory used by Excel: 17444864
Memory used by Excel: 17752064
Memory used by Excel: 18055168
Memory used by Excel: 18358272
Memory used by Excel: 18657280

I immediate ran the code a second time:

Memory used by Excel: 14700544
Memory used by Excel: 19308544
Memory used by Excel: 19603456
Memory used by Excel: 19902464
Memory used by Excel: 20213760
Memory used by Excel: 20525056
Memory used by Excel: 20832256
Memory used by Excel: 21143552
Memory used by Excel: 21430272
Memory used by Excel: 21745664
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22061056
Memory used by Excel: 22413312
Memory used by Excel: 22745088
Memory used by Excel: 23068672
Memory used by Excel: 23384064
Memory used by Excel: 23736320
Memory used by Excel: 24092672
Memory used by Excel: 24449024
Memory used by Excel: 24805376
Memory used by Excel: 25169920
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25522176
Memory used by Excel: 25890816
Memory used by Excel: 26247168
Memory used by Excel: 26603520
Memory used by Excel: 26980352
Memory used by Excel: 27336704
Memory used by Excel: 27697152
Memory used by Excel: 28053504
Memory used by Excel: 28409856
Memory used by Excel: 28770304
Memory used by Excel: 29126656

Here is my code:

Option Explicit

Private Declare Function PdhVbOpenQuery Lib "pdh.dll" _
(ByRef QueryHandle As Long) As Long
Private Declare Function PdhCloseQuery Lib "pdh.dll" _
(ByVal QueryHandle As Long) As Long
Private Declare Function PdhVbAddCounter Lib "pdh.dll" _
(ByVal QueryHandle As Long, ByVal CounterPath As String, _
ByRef CounterHandle As Long) As Long
Private Declare Function PdhRemoveCounter Lib "pdh.dll" _
(ByVal CounterHandle As Long) As Long
Private Declare Function PdhCollectQueryData Lib "pdh.dll" _
(ByVal QueryHandle As Long) As Long
Private Declare Function PdhVbGetDoubleCounterValue Lib "pdh.dll" _
(ByVal CounterHandle As Long, ByRef CounterStatus As Long) As
Double
Private Declare Function PdhVbIsGoodStatus Lib "pdh.dll" _
(ByVal StatusValue As Long) As Long
Private Declare Function PdhVbGetOneCounterPath Lib "pdh.dll" _
(ByVal PathString As String, ByVal PathLength As Long, _
ByVal DetailLevel As Long, ByVal CaptionString As String) As Long
Private Declare Function PdhVbCreateCounterPathList Lib "pdh.dll" _
(ByVal DetailLevel As Long, ByVal CaptionString As String) As Long
Private Declare Function PdhVbGetCounterPathFromList Lib "pdh.dll" _
(ByVal Index As Long, ByVal Buffer As String, _
ByVal BufferLength As Long) As Long
Private Declare Function PdhVbGetCounterPathElements Lib "pdh.dll" _
(ByVal PathString As String, ByVal MachineName As String, _
ByVal ObjectName As String, ByVal InstanceName As String, _
ByVal ParentInstance As String, ByVal CounterName As String, _
ByVal BufferSize As Long) As Long

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim max As Integer
Dim r As Long
Dim hPDHQuery As Long 'Handle to performance monitor
query
Dim hPDHCounter As Long 'Handle to performance monitor
counter
Dim strCounterPath 'Path to performance monitor
counter
Dim lngCounterStatus As Long 'Status of counter when checked
Dim dblPrivateBytes As Double 'Value of counter when checked

' Open Excel
Dim xl As Object
Set xl = CreateObject("Excel.Application")

'Initialize PDH query object.
r = PdhVbOpenQuery(hPDHQuery)

'Initialize counter.
strCounterPath = "\\JAMIEC2\Process(Excel)\Private Bytes"
r = PdhVbAddCounter(hPDHQuery, strCounterPath, hPDHCounter)

'Gather data.
r = PdhCollectQueryData(hPDHQuery)

'Get counter value and process data.
dblPrivateBytes = PdhVbGetDoubleCounterValue(hPDHCounter, _
lngCounterStatus)
If PdhVbIsGoodStatus(lngCounterStatus) Then
Debug.Print "Memory used by Excel: " & CLng(dblPrivateBytes)
Else
Debug.Print "Invalid data."
End If

max = 10 * 5
ReDim alngPrivateBytes(max)

Dim wb As Object
Dim strTargetWB As String
For i = 1 To 5
For j = 1 To 10
If j = 1 Then
If i Mod 2 = 1 Then
Set wb = xl.Workbooks.Open("C:\db.xls")
Else
wb.Close False
End If
End If
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
cn.CursorLocation = adUseClient
cn.Open

strTargetWB = "C:\db" & ((i - 1) * 10) + j & ".xls"
On Error Resume Next
Kill strTargetWB
On Error GoTo 0

cn.Execute _
"SELECT * INTO" & _
"[Excel 8.0;Database=" & strTargetWB & ";]" & _
".test FROM [Excel 8.0;Database=C:\db.xls;].[Sheet1$]"

r = PdhCollectQueryData(hPDHQuery)

'Get counter value and process data.
dblPrivateBytes = PdhVbGetDoubleCounterValue(hPDHCounter, _
lngCounterStatus)

'Process data.
If lngCounterStatus = 0 Then
Debug.Print "Memory used by Excel: " &
CLng(dblPrivateBytes)
alngPrivateBytes(((i - 1) * 10)) = CLng(dblPrivateBytes)
Else
Debug.Print "Invalid data."
alngPrivateBytes(0) = 0
End If
cn.Close
Set cn = Nothing ' just for Nate <g>
Next
Next
' Debug.Print "Total increase for " & max & " iterations: " & _
' (CStr(alngPrivateBytes(max) - alngPrivateBytes(0)))
' Debug.Print "Average increase per iteration: " & _
' (CStr((alngPrivateBytes(max) - alngPrivateBytes(0))) / max)

'Clean up.
r = PdhRemoveCounter(hPDHCounter)
r = PdhCloseQuery(hPDHQuery)

' Close Excel
xl.Quit
Set xl = Nothing

End Sub


Jamie.

--

 

[Guest]

Hello Jamie,

Actually, I did do some testing. Usually do, but I'm typically not a memory
leak hunter, so this variety of testing is somewhat foreign to me, i.e., I
don't usually pull up perfmon.exe to check on process bytes.

Why do I not try to break to ADO for you? Honestly, it's not how I prefer to
spend my time. Can I? Not sure.

Have some respectable programmers (in my estimation) recommend taking out
your own trash versus relying on VB[A] to do it? Yes, for whatever reason, be
it a DAO, Excel, ADO Error, whatever, good enough for me, it seems like
reasonable defense.

It's mentioned again here:
http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp

Not my findings, but I'll go with the heard on this one. Like declaring
variables, it makes sense to me to explicitly destroy that which I create, so
I do and move on with life. Do you have to declare variables in VB[A]? I'll
leave you to your ongoing experimentation.

Funny you should mention the discord between the two threads. In the other
thread it seems you're arguing that ADO is stable and to be trusted, and
here, well, we're seeing something that's not good, whether it's Excel, Jet
or ADO.

In any case, back with the quandary at hand, you might be right.

I got playing with a loop last night myself, it looked something like the
following:

Sub ExToEx()
Dim cn As Object, i As Long
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
For i = 1 To 1000000
Kill "c:\temp\book1.xls"
cn.Execute "SELECT Col1, Col2 INTO " & _
"[Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _
& " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value
Next
cn.Close: Set cn = Nothing
End Sub

And I played around with binding in the loop as well. I wasn't pleased with
the results.

When I did this by hand, yesterday it appeared, by my eye, to stabilize.
When I continuously slammed Jet with the loop, memory was getting chewed up
with each pass.

When I closed the Workbook, memory appeared to crawl back, very slowly. And
when I closed the Excel App, I got a giant dose of memory back, which sounds
inconsistent with what you're saying, but, my observation based on limited
testing.

I have the latest SP for Jet, it appears that the cache might be the issue,
it doesn't appear to be cleared out and hangs with the App.

Is it a classic memory leak? Not sure, but it's not good and is a shame.

Cheers,
Nate Oliver

 

[Jamie Collins]

I wasn't pleased with
the results.

Is it a classic memory leak? Not sure, but it's not good and is a

shame.

Sincere thanks for this.

In the other
thread it seems you're arguing that ADO is stable and to be trusted, and
here, well, we're seeing something that's not good, whether it's Excel, Jet
or ADO.

I'm trying to concentrate on specifics. I've seen bad things happen
when using ADO + Jet + an open Excel workbook, so I view it as an
unstable aspect of ADO. Likewise isolated and specific unstable aspects
of DAO, Excel, etc. I've yet to see bad things happen when ADO objects
are closed/released implicitly and/or in the 'wrong' order, so I
consider it a stable aspect of ADO. I've not seen or heard of bad
things happening when the VB garbage collector is trusted to do its
work.

Jamie.

--