Excel -Access DB -ADO. Memory Leak-> System Resource Exceeded

Question

Im using Excel VBA to clean up large csv files. For that i load the csv files into access database then using sql queries i perform all data transformation activities. So the process in abstract goes like this

Open Excel --> On Start click Crate an access database --> Load csv files to different tables --> Do different DDl as well as DML statements on database using adoddb connection --> output final data.

The problem I am facing here is that the memory usage always goes up by excel. It seems the access db processing is also added to excel itself. So ultimately i get the error "System Resource Exceeded".

Each time a query is executed. the memory usage goes high up and never comes down. The queries are on around 10k to 100k records in 3-4 tables.

Why does memory usage never comes down?

Every time i do a ddl/dml query i open adodb connection and close it. I close all recordsets after use and set to nothing. But still the memory usage never comes down.

Saw different articles related. But most are discussing about data in the same excel file. In my case no data is kept in memory or in excel file.

I saw one article from Microsoft here which also talks about the data in excel itself. https://support.microsoft.com/en-us/kb/319998

Does anybody know any workaround for this please?

For eg: to load data to a table from csv file i use the below code

 StrSql = "SELECT * into " & TableName & " FROM [Text;FMT=Delimited;HDR=YES;DATABASE=" & DSPath & "].[" & DSName & "]"
    ExecuteSQL StrSql


Private Function ExecuteSQL(Sql As String) As Long
  Dim Con As ADODB.Connection
  Dim I As Long

  Connect Con
  Con.Execute Sql, I
  ExecuteSQL = I
  CloseCon Con
End Function

Public Sub CloseCon(ByRef Con As ADODB.Connection)
  If Not Con Is Nothing Then
     If Con.State = adStateOpen Then
        Con.Close
        Set Con = Nothing
     End If
  End If
End Sub

Public Sub Connect(ByRef Con As ADODB.Connection)
Dim ConStr As String

If Not Con Is Nothing Then
    If Con.State = adStateOpen Then
        Exit Sub
    End If
End If
On Error GoTo err
    CloseCon Con
    Set Con = New ADODB.Connection
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile & ";Persist Security Info=False"
    Con.Open ConStr, , , -1
    Exit Sub
err:
End Sub

This has helped a little.

Instead of using a separate connection object each time opening and closing i tried to use a public connection object which is open from the beginning and closed only when the process is completed. This way the memory consumption has reduced and the process runs for much more time

Answer 1

USE THIS

   strSystemIDT = Trim(DirPath.Path) + "\???.accde" 
   With IDTdbCnnctn
       .ConnectionTimeout = 30
       .Open "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" & Trim(strSystemIDT) & ";Jet OLEDB:Database password=" & Trim(???) & ""
       .Properties("Jet OLEDB:Max Locks Per File") = 50000 
       .Properties("Jet OLEDB:Exclusive Async Delay") = 0
       .Properties("Jet OLEDB:Flush Transaction Timeout") = 25000
       .Properties("Jet OLEDB:Lock Delay") = 10 
       .Properties("Jet OLEDB:Lock Retry") = 20
       .Properties("Jet OLEDB:Max Buffer Size") = 100000
       .Properties("Jet OLEDB:Page Timeout") = 25000
       .Properties("Jet OLEDB:Shared Async Delay") = 0
       .Properties("Jet OLEDB:Recycle Long-Valued Pages") = 0
   End With

Answer 2

Instead of manually attempting to gracefully manage objects with helper functions I would recommend using a With block to control the scope of all objects in VBA.

Here is an example using your code:

Private Function ExecuteSQL(DBFile As String, Sql As String) As Long
    Dim I As Long

    With Connect(DBFile)
        .Execute Sql, I
        .Close
    End With
    
    ExecuteSQL = I
End Function

Public Function Connect(ByVal DBFile As String) As ADODB.Connection
    On Error GoTo err
    Set Connect = New ADODB.Connection
    
    Dim ConStr As String
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile & ";Persist Security Info=False"
    Connect.Open ConStr, , , -1
    
    Return
    
err:

End Function

Answer 3

Your code looks sound. The issue is the confirmation by Microsoft in your reference: "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.

So we must quit Excel now and then to reclaim resources.

1. "Quit Excel" means that the current workbook from which you are working must be closed, or

2. "Quit Excel" means to quit all instances of Excel so Excel is effecively removed from memory.

Ad. 1: in this case you can create a "parent workbook" that starts another workbook containing a part of the ADODB processing. It quits after the partial processing and your parent starts a new, other, workbook that continues the processing, etcetera. Use some smart cut & paste to tune when to quit each workbook.

Ad 2: in this case you use e.g. Word to start a new instance of Excel.Application and proceed in the same manner as under 1. Just hope that MS-Office is not so integrated that the ADO DLL will not quit when any Office program is running...

Of course, complaining with the Microsoft confirmed bug in hand to your IT department to have the Access UI installed might even be better.