[AccessD] CopyFromRS memory Leak in Excel VBA

Darryl Collins darryl at whittleconsulting.com.au
Wed Mar 19 19:04:53 CDT 2014


Right now these are final finished and polished reports, so the formatting is critical - indeed each sheet is formatted as it is created.

The process currently works like this.

MS Access call a xlsb code enabled XL template.
Once opened the VBA code in XL takes over and does its thing.

That 'thing' it does is downloading pre made queries from SQL Server Directly or x-tabs from MS Access and formats them into a final client presentable version.  Each imported report is formatted correctly, has colours and formulae added as required et al.

Once the import has finished, MS Access takes over again.
MS Access saves the xlsb file, converts it to an xlsx format (to strip out all of the code) and resaves it as an xlsb sans any VBA modules (which makes it faster and more responsive than an XLSX format).

The only option I can see right now is to move all the logic Access side.  As I said, doable, but at a cost. :-/

Have the code in XL makes everything much easier, especially for testing and audit.  Loathe to remove it if it can be avoided.

Cheers
Darryl.







-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, 20 March 2014 10:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] CopyFromRS memory Leak in Excel VBA

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
> 


-- 
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