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

Article translations Article translations
Article ID: 319998 - View products that this article applies to.
This article was previously published under Q319998
Expand all | Collapse all

On This Page

SYMPTOMS

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.

RESOLUTION

The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

If possible, query the Excel worksheet only while the file is not open in Excel.

If the worksheet must remain open (for example, to allow dynamic recalculation of worksheet values on an ongoing basis) use one of the following methods to work around the behavior:

Method 1

  • 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

Method 2

  • Use the SaveCopyAs method of the Workbook object in the Excel object model to programmatically save the open Excel file under a new name. You can then query the copy of the file that you previously saved under a new name from the ADO application.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create or locate an Excel worksheet with rows and columns of data that can be queried using ADO. For example, you can use Excel to import the Customers table from the sample Northwind Access database.
  2. In Visual Basic 6.0, create a Standard EXE project. By default, Form1 is created. Set a reference to ADO.
  3. Add a module to the project, and then enter the following declarations for application programming interface (API) calls that allow you to check memory usage using Performance Monitor counters:
    Option Explicit
    
    'Performance monitor functions for Visual Basic from PDH.DLL
    Declare Function PdhVbOpenQuery Lib "pdh.dll" _
        (ByRef QueryHandle As Long) As Long
    Declare Function PdhCloseQuery Lib "pdh.dll" _
        (ByVal QueryHandle As Long) As Long
    Declare Function PdhVbAddCounter Lib "pdh.dll" _
        (ByVal QueryHandle As Long, ByVal CounterPath As String, _
        ByRef CounterHandle As Long) As Long
    Declare Function PdhRemoveCounter Lib "pdh.dll" _
        (ByVal CounterHandle As Long) As Long
    Declare Function PdhCollectQueryData Lib "pdh.dll" _
        (ByVal QueryHandle As Long) As Long
    Declare Function PdhVbGetDoubleCounterValue Lib "pdh.dll" _
        (ByVal CounterHandle As Long, ByRef CounterStatus As Long) As Double
    Declare Function PdhVbIsGoodStatus Lib "pdh.dll" _
        (ByVal StatusValue As Long) As Long
    Declare Function PdhVbGetOneCounterPath Lib "pdh.dll" _
        (ByVal PathString As String, ByVal PathLength As Long, _
        ByVal DetailLevel As Long, ByVal CaptionString As String) As Long
    Declare Function PdhVbCreateCounterPathList Lib "pdh.dll" _
        (ByVal DetailLevel As Long, ByVal CaptionString As String) As Long
    Declare Function PdhVbGetCounterPathFromList Lib "pdh.dll" _
        (ByVal Index As Long, ByVal Buffer As String, _
        ByVal BufferLength As Long) As Long
    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
    					
  4. Add a command button to Form1, and then insert the following code in the Click event. This code repeatedly queries an Excel file and displays memory usage in the Debug (Immediate) window.
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim i 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
        Set cn = New ADODB.Connection
    'Jet connection string.
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & App.Path & "\test.xls;" & _
            "Extended Properties=Excel 8.0"
        'Initialize PDH query object.
        r = PdhVbOpenQuery(hPDHQuery)
        'Initialize counter.
    'Edit the value of the "strCounterPath" variable.
        strCounterPath = "\\<computername>\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
    'Edit the value of the "max" variable.
        max = 100
        ReDim alngPrivateBytes(max)
        For i = 1 To max
            Set rs = New ADODB.Recordset
            rs.CursorLocation = adUseClient
    'Edit the query to reflect the name of the worksheet.
            rs.Open "SELECT * FROM [Customers$]", cn
            Do Until rs.EOF
                rs.MoveNext
            Loop
            rs.Close
            Set rs = Nothing
            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) = CLng(dblPrivateBytes)
            Else
                Debug.Print "Invalid data."
                alngPrivateBytes(0) = 0
            End If
        Next
        cn.Close
        Set cn = Nothing
        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)
    					
  5. Make the following changes in the code:

    • Edit the Jet connection string to reflect the location of the Excel test file.
      Note: It is commented as follows: 'Jet connection string.
    • Edit the value of the "strCounterPath" variable to use the local workstation name.
      Note: It is commented as follows: 'Edit the value of the "strCounterPath" variable.
    • Edit the query to reflect the name of the worksheet in the file with the test data.
      Note: It is commented as follows: 'Edit the query to reflect the name of the worksheet.
    • Edit the value of the "max" variable for the desired number of repetitions.
      Note: It is commented as follows: 'Edit the value of the "max" variable.
  6. Run the Excel application, and then open the test Excel file.
  7. Run the Visual Basic application. Note that there is a steady increase in memory that is used by the Excel process, even though the ADO objects have been closed and released.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
257819 How To Use ADO with Excel Data from Visual Basic or VBA
For additional information about the use of performance counters from Visual Basic, click the following article number to view the article in the Microsoft Knowledge Base:
296526 INFO: Collecting Performance Data Using PDH APIs from Visual Basic
For additional information about Excel export options, click the article numbers below to view the articles in the Microsoft Knowledge Base:
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
246335 How To Transfer Data from an ADO Recordset to Excel with Automation

Properties

Article ID: 319998 - Last Review: February 12, 2007 - Revision: 3.3
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft OLE DB Provider for Jet 4.0
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbado kbmemory kbperformance kbprogramming kbbug kbiisam kbjet kbnofix kbprovider KB319998

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com