Darryl Collins
darryl at whittleconsulting.com.au
Wed Mar 19 19:13:28 CDT 2014
This idea has merit, although I was going to just the INSERT INTO to create all of the exported xtabs as individual files rather than a text file. Once they are all created I can then get the XL template to sweep them up from there and do its formatting and cleaning up etc. This approach is still an option, probably the one I will end up taking if I don't get a better solution. Annoying as 98% of the time this thing is fast, stable and agile. It is only a couple of the most extreme reporting scenarios I am hitting this issue. Meh! 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