Bill Benson
bensonforums at gmail.com
Thu Mar 20 06:55:02 CDT 2014
I might try automating a new instance of excel, dropping info there save, close, re-open in 1st instance and cherry pick results. Might do more harm than good, or might save the day. I haven't tried it and am not sure the impact but you did say you were open to new ideas. Alas, no one has hired me in months and I have no big recordsets, although soon my medical bills will be approaching one. BB On Mar 19, 2014 8:14 PM, "Darryl Collins" <darryl at whittleconsulting.com.au> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >