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

im using excel vba clean large csv files. load csv files access database using sql queries perform data transformation activities. process in abstract goes this

open excel --> on start click crate access database --> load csv files different tables --> different ddl dml statements on database using adoddb connection --> output final data.

the problem facing here memory usage goes excel. seems access db processing added excel itself. error "system resource exceeded".

each time query executed. memory usage goes high , never comes down. queries on around 10k 100k records in 3-4 tables.

why memory usage never comes down?

every time ddl/dml query open adodb connection , close it. close recordsets after use , set nothing. still memory usage never comes down.

saw different articles related. discussing data in same excel file. in case no data kept in memory or in excel file.

i saw 1 article microsoft here talks data in excel itself. https://support.microsoft.com/en-us/kb/319998

does know workaround please?

for eg: load data table csv file use below code

 strsql = "select * " & tablename & " [text;fmt=delimited;hdr=yes;database=" & dspath & "].[" & dsname & "]"     executesql strsql   private function executesql(sql string) long   dim con adodb.connection   dim long    connect con   con.execute sql,   executesql =   closecon con end function  public sub closecon(byref con adodb.connection)   if not con nothing      if con.state = adstateopen         con.close         set con = nothing      end if   end if end sub  public sub connect(byref con adodb.connection) dim constr string  if not con nothing     if con.state = adstateopen         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 little.

instead of using separate connection object each time opening , closing tried use public connection object open beginning , closed when process completed. way memory consumption has reduced , process runs more time

your code looks sound. issue confirmation microsoft in reference: "the memory used ado queries cannot reclaimed closing , releasing ado objects. way release memory quit excel.

so must quit excel , reclaim resources.

1. "quit excel" means current workbook working must closed, or

2. "quit excel" means quit instances of excel excel effecively removed memory.

ad. 1: in case can create "parent workbook" starts workbook containing part of adodb processing. quits after partial processing , parent starts new, other, workbook continues processing, etcetera. use smart cut & paste tune when quit each workbook.

ad 2: in case use e.g. word start new instance of excel.application , proceed in same manner under 1. hope ms-office not integrated ado dll not quit when office program running...

of course, complaining microsoft confirmed bug in hand department have access ui installed might better.