[AccessD] CopyFromRS memory Leak in Excel VBA

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
>


More information about the AccessD mailing list