title: Excel -Access DB -ADO. Memory Leak-> System Resource Exceeded
body: <p>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</p>
  
  <p><em>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.</em></p>
  
  <p>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 <code>"System Resource Exceeded"</code>.</p>
  
  <p>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.</p>
  
  <p><strong>Why does memory usage never comes down?</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I saw one article from Microsoft here which also talks about the data in excel itself.
  <a href="https://support.microsoft.com/en-us/kb/319998" rel="nofollow">https://support.microsoft.com/en-us/kb/319998</a></p>
  
  <p>Does anybody know any workaround for this please?</p>
  
  <p>For eg: to load data to a table from csv file i use the below code</p>
  
  <pre><code> StrSql = "SELECT * into " &amp; TableName &amp; " FROM [Text;FMT=Delimited;HDR=YES;DATABASE=" &amp; DSPath &amp; "].[" &amp; DSName &amp; "]"
      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=" &amp; DBFile &amp; ";Persist Security Info=False"
      Con.Open ConStr, , , -1
      Exit Sub
  err:
  End Sub
  </code></pre>
  
  <p><strong>This has helped a little.</strong></p>
  
  <p><strong>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</strong></p>