Darryl Collins
darryl at whittleconsulting.com.au
Thu Mar 20 19:05:17 CDT 2014
Hi Bill, The '(Permanently) move the XL code modules into the Access FE from XL' is the last resort option - Doing this would allow me to open a VBA free XL template and do all the heavy lifting / formatting of XL from Access. It would also give me the ability to save, close, reopen and pick up where I left off in the XL file if necessary. But frankly, that is a hell of a lot of dicking around for an issue that only impacts a tiny amount of the output. Looking for a lazier solution. Right now the XL template has code in it which does all of that work. Keeping it native in XL is much nicer - it makes things faster when running, easier when debugging (it can be run and tested independently of any other applications) and also allow XL to talk to both SQL Server and MS Access at the same time. Much of the data come directly from SQL Server and is never seen in the Access FE, but there are a whole series of x-tab reports that I pull in via Access - these seem to be the bothersome bits. There is a known bug that causes memory leaks. XL doesn't release the memory correctly, even though you have closed and set the connection properties to zero. This is only an issue when I am doing hundreds of these in a row - otherwise it is seamless. I have used copyfromRS for years without any problems - it is generally rock solid stable and reliable - this is the first time I have come across any issue. To quote MS: "The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel". Anyway, I have few other cunning tricks I am going to try out first. Not done with this game yet! Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson Sent: Friday, 21 March 2014 4:43 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] CopyFromRS memory Leak in Excel VBA 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com