EXCEL produces a virtual memeory error after approximately 8 hours of continual use. The EXCEL process in task manager shows usage sometimes in excess of 100,000K.
From a Visual Basic standpoint, my application is relatively simple. It is an automated Rolodex allows a user to search a database (SQL Server) for names and paste the associated telephone numbers into an EXCEL Spreadsheet.
There are a few additional features that allow a user to build filters and bull-lists.
The application has a class object that performs all the database interaction. The class Object uses the ADO connection and recordset Objects to connect to the database.
Recordsets are converted to a Complex array structure (Array of Arrays) which are passed on to the application. The recordset and connection Objects are then destroyed within the class. Recordset size is 3 fields max. and around 5,000 rows.
The app is fired from an imbedded button on "Sheet1" of the WorkBook. Once the app pastes the phone numbers, The class object is destroyed and the program Ended. The Spreadsheet persists.
The master application resides on a server where a shortcut is created from each of the client workstations.
What we have found so far:
I had a senior colleague look at the code with me and together we did not find any sloppy housekeeping with regard to the ADO Objects.
We placed the app in a loop and were unable to simulate the memeory escalation problem on our test machines.
Perhaps we are dancing around the problem or are too close to see the obvious.
Minimizing the application will release the memory usage!
Does anyone have any leads as far as what we should be considering? Is EXCEL/VBA doing a poor job of managing memory for the App? Is this a diplay thing??
Does that fact that the app is share from a central server have any bearing? Could there be underlying database issues that are actually causing this??
Again the problem is not immediate, it takes about 8 hours or so of constant use to create the issue.
Any help would be appreciated.