Bill Benson
bensonforums at gmail.com
Thu Mar 20 12:43:19 CDT 2014
Yeah that is what I thought Darryl was saying but then he said he would have to move the modules from excel to accesd, and I got... What was it I got? I am mixed up what I got... oh yeah, confused. On Mar 20, 2014 11:05 AM, "John W Colby" <jwcolby at gmail.com> wrote: > I have not followed this thread but I used to work on a spreadsheet I > wrote that had the modules for Excel itself inside of the spreadsheet. I > would then open the workbook and call code in the spreadsheet. I did this > because the sheet was formatted and stuff and then the code was local > access / cell references instead of building all that code out in Access. > It worked just fine. > > John W. Colby > > Reality is what refuses to go away > when you do not believe in it > > On 3/20/2014 10:58 AM, Bill Benson wrote: > >> My first response may have missed the fact (?) you were automating Excel >> from Access? But then again you write that the code modules are in Excel >> which seems like you are starting from the Excel workbook. >> >> But the you would not be writing about shutting down Excel from Access. >> >> But then again you write it is an Access FE to a SQL Server BE, so that >> indicates you are working from Access and automating Excel after all. >> >> ??? >> >> I am really confused Darryl... >> >> Can you clear up your runtime environment and hand offs for my slow brain? >> 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 >>> >>> > > --- > This email is free from viruses and malware because avast! Antivirus > protection is active. > http://www.avast.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >