Paul Hartland
paul.hartland at googlemail.com
Thu Mar 20 09:21:13 CDT 2014
Having never coded Access to open an Excel file then the Excel file takes over then Access taking over again i haven't really come across this issue but never had any problems with CopyFromRS either, my thoughts would be that something your opening is being shut down and released from memory correctly which is why them memory usage keeps going up and up, if you could supply the code on or offline and maybe the actual database and excel templates I am off out for an hour, but as I am currently unemployed would be more than happy to take a look for you to see if I can see anything, sometimes a fresh pair of eye will spot something that's been missed. Paul On 20 March 2014 11:55, Bill Benson <bensonforums at gmail.com> wrote: > 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 > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Paul Hartland paul.hartland at googlemail.com