[AccessD] CopyFromRS memory Leak in Excel VBA

Stuart McLachlan stuart.mclachlan at gmail.com
Wed Mar 19 18:55:09 CDT 2014


How important is formatting?

An alternative could be to dump the data to a tab delimited file and then use Office 
automation to open/save the file in Excel.

On 19 Mar 2014 at 23:18, Darryl Collins wrote:

> Hi Guys and Girls.
> 
> Never had a problem with CopyFromRS before, but now I seem to bumped
> into an issue with Excel bleeding memory until it jams up whole
> system.  It only happens on the (few and rare) larger reports.  By
> larger I mean I will be performing CopyFromRS several hundred times in
> a row, and some of the RS are large (100K plus rows x 30 columns -
> these are x-tabs imported from Access - not all of them are that big
> of course virtually none of them will be close to fully populated). 
> The resulting XL file isn't that big though (maybe 50 MB at the end).
> 
> MS Access is just a FE to a SQL Server Database.  Nearly all the data
> is in SQL Server and it is really only the x-tabs reports in the
> Access FE that we refer too in this instance.
> 
> Watching the performance manager whilst this is running you can see
> the memory constantly creeping up until it is full (where XL dies with
> an "out of Memory" Error.  This is a known issue too according to Prof
> Google and Microsoft, but it looks like MS have been too busy making
> things look pretty rather than fixing bugs for the last decade or so.
> 
> Things which were already in place:
> 
> In the existing solution, all RS and connections are opened, close and
> set to nothing correctly - although according the MS that doesn't help
> (as it is part of the problem, XL doesn't release the memory correctly
> once the object is closed and removed).
> 
> The only solution MS offer is to close the XL application and the
> reopen it.  This will require a fair bit of dicking around on my
> behalf - mostly moving all of the existing XL code modules into Access
> and retweaking them.   Then I can close the XL app, reopen it and pick
> up from where I left off.  Whilst this is doable, it is a lot of time
> and effort to fix an issue that only deals with 2% or so of all the
> reporting needs we have.  Hmmmmm....
> 
> 
> Changes I have put in which have helped (so far):
> 
> Replaced all the CopyFromRS code with an INSERT INTO function instead,
> which pushes the x-tab data into a new Excel Workbook, from where I
> copy it from there to the working XL workbook and then use KILL to
> remove the temp XL file created by the INSERT INTO.  That slowed down
> the memory creep, but it is still present.
> 
> I added in a few regular saves in the process , particularly after
> adding in new worksheets.  Whilst this slows things down it did slow
> down the memory creep, but still no cigar.
> 
> So...
> 
> Open to ideas here.  Anyone got anything they can suggest or I might
> have missed I am all eyes and ears.
> 
> Cheers
> Darryl
> 
> 
> 
> 
> 
> Darryl Collins
> Whittle Consulting Pty Ltd
> Suite 8, 660 Canterbury Rd
> Surrey Hills, VIC, 3127
> 
> p: +61 3 9898 3242
> m: +61 418 381 548
> f: +61 3 9898 1855
> e:
> darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au
> > w:
> www.whittleconsulting.com.au<http://www.whittleconsulting.com.au/>
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 




More information about the AccessD mailing list