[AccessD] Full Circle..

Well.  Fun few days.  Re: The suspected ADO memory leak with Excel issue I was having.  Firstly, I am not even sure the issue was related to an ADO memory leak.  Using other methods Excel would still crash out, although it took longer to happen.  I suspect Excel just doesn't like working dynamically with really large workbooks (60 MB+ in size).

Here is what I tried.

INSERT INTO

Replaced all CopyFromRS lines with an INSERT INTO method instead.  This was a bit more stuffing around as it created the xtab report into a new Excel Worksheet, which was then copied into the XL Template and the 'new' workbook destroyed.

This increased slightly the number or xtabs copied before Excel bugged out on memory issues.


Using DAO for CopyFromRS

Replaced all ADO connections for this process with DAO.  The DAO code was a bit faster (but not hugely so), but it also got the wobbles after a while. It just wasn't reliable and would frequently either lose the connection to the backend DB or get an "Microsoft Office Excel is waiting for another application to complete an OLE action" error.  Not practical.  Probably works ok for a few, but would nearly always get flakey on many.  Given ADO never gave me any stability issues I can see no point in going down this path.  Stability and reliability trumps speed.


ALSO TRIED.

Adding in DoEvents and Saves periodically - made no difference.


FINAL SOLUTION

Keep using ADO which is rock solid and fast enough and put in an option for the users to produce the final report in components if they get the (very rare) 'Out of Memory' issue.

Oh well....  Such is life.  Spent a week to end up where I was before - not a waste though as I learnt lots along the way : )


Cheers
Darryl.