Is the ADO connection memory leak problem fixed in Microsoft.ACE.OLEDB.12.0?

Hi everyone, I'm new here. Been an Excel user for 10 years and over the time learned to write some basic (by basic I mean really really basic) VBA to support my work.

I'm exploring the idea to use ADO connection to query an open workbook, which is not new at all. I've avoided it in the past because I read about the bug of memory leak, I understand there are two possible workarounds (SELECT INTO, and SAVECOPYAS), but I find them not very practical in my situation.

Recently I read somewhere that with Microsoft.ACE.OLEDB.12.0 this memory leak is no longer a problem, so I created a workbook with ADO and several tables all less than 1000 rows and 20 columns, I then ran some queries on them over and over again and did not experience the memory leak issue. I checked VBE and memory usage, all seemed normal.

Is the memory leak issue really fixed with Microsoft.ACE.OLEDB.12.0? That would be great as I find the ADO extremely useful.

Your help is much appreciated. I'm using Excel 2010 SP1 32-bit.

Thanks!

Originally Posted by Kyle123

No, it isn't fixed. The issue still remains

There's some code here to test it yourself https://support.microsoft.com/en-us/kb/319998 just swap out the connection string

Yeah I already read this article when I was doing the research, but noticed at the bottom it says applies to Excel 97-2002 and OLE DB Provider for Jet 4.0, so not sure if it's still true for ACE.OLEDB.12.

In the workbook I created, I tried to throw everything I know on the queries (LEFT JOIN, INNER JOIN, UPDATE, INSERT INTO, nested SELECT..., reading and writing to the workbook with SQL), but everything seemed fine, either I was extremely lucky or the symptom will only be triggered by certain arrangement and I happened to have avoided it so far, maybe...

So anyone can give a firm yes/no answer to this?

I can reproduce it...

Originally Posted by Kyle123

I can reproduce it...

Yes I don't doubt that, if you've created a file to reproduce the symptom, would you be so kind to post it here for download? I want to see why it's causing problem there but not in my file. Sorry I'm being lazy here.

Does running DAO instead of ADO on an open spreadsheet overcome this issue?

Thanks for responding Kyle. Good call on DAO. I overlooked that. I've used DAO and ADO to pull data from Access, but never from an open spreadsheet.

For my current situation, it looks like OP and I are trying to overcome the same problem. I'd like to pull data from one part of a spreadsheet and either filter or sum based on certain criteria. SQL seems easiest for this specific task. It seems one workaround is to save the table data to a text file, or a temporary workbook, and run SQL on that. I'm not sure this is what I want to do but I'll give it a shot, unless anyone has a better suggestion.

Migrating data to Access (or another database) is not an option at this time.

@OP, have you found a satisfactory work-around?