+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    03-15-2016
    Location
    LA
    MS-Off Ver
    Excel 14.0.6023.1000 (32-bit)
    Posts
    3

    Question 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!

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

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

    I cannot answer your question definitively but I haven't found the problem in recent years. It certainly was the case that bad programming contributed to memory leakage. So maybe I instinctively avoid the scenarios or maybe the problems have gone away. But maybe my scenarios are different.
    How is your application intended to be used?


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

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

    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
    Last edited by Kyle123; 03-16-2016 at 02:25 AM.

  4. #4
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

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

    Quote Originally Posted by Kyle123 View Post
    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
    Kyle123 well done.

  5. #5
    Registered User
    Join Date
    03-15-2016
    Location
    LA
    MS-Off Ver
    Excel 14.0.6023.1000 (32-bit)
    Posts
    3

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

    Quote Originally Posted by Kyle123 View Post
    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?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

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

    I can reproduce it...

  7. #7
    Registered User
    Join Date
    03-15-2016
    Location
    LA
    MS-Off Ver
    Excel 14.0.6023.1000 (32-bit)
    Posts
    3

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

    Quote Originally Posted by Kyle123 View Post
    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.

  8. #8
    Registered User
    Join Date
    03-21-2016
    Location
    US
    MS-Off Ver
    2010
    Posts
    2

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

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

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

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

    I think so, but I seem to think there's no 64-bit DAO so you may have issues.

    What are you actually trying to do? Can't you just copy the data to another workbook and query that? Or better still use a Pivot table?

    P.S Sorry, hand't noticed that you aren't the OP
    Last edited by Kyle123; 03-21-2016 at 11:42 AM.

  10. #10
    Registered User
    Join Date
    03-21-2016
    Location
    US
    MS-Off Ver
    2010
    Posts
    2

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

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Memory resident macro with multiple web connection problem.
    By shailew in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2014, 02:23 AM
  2. [SOLVED] Leave OLEDB connection open
    By ngirrens in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-09-2012, 02:30 PM
  3. Excel 2007 : Add data via an OLEDB connection
    By pepi in forum Excel General
    Replies: 0
    Last Post: 08-03-2011, 06:29 AM
  4. Querytable OLEDB connection
    By mrcoffee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2011, 12:04 PM
  5. [SOLVED] Define OLEDB connection string
    By John in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2011, 08:42 AM
  6. OLEDB Connection in
    By mishra.ananda in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2009, 02:53 AM
  7. [SOLVED] oledb connection string problem in vb.net
    By Manoj Nair in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2006, 03:45 AM
  8. [SOLVED] Connection to Excel from .Net using Oledb
    By c23chaos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2006, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1