Darryl Collins
darryl at whittleconsulting.com.au
Wed Mar 19 18:18:11 CDT 2014
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/>