Arrays/memory issues

[GSMike]

We frequently have mdb bloating problems because some of the reports we run are processed with calculation fields in queries that post the data to a table which, in turn, are used to further process the data again (and maybe again).
So, every time the reports are run, this data is deleted (with maybe a delete query or a make table query that effectively does the same).
So, I have started processing the data in arrays, which are non-persistent in the mdb, they only exist in memory.
Does anyone have any insight into potential resource issues with data being processed in memory as opposed to being processed within Access? Example: If I have 126M RAM that generally is used at a level of about 3%, does that mean that I can safely "shove" 50M of data into an array to be processed by memory?
If so, if I have a resource-use spike (like if someone launches an app while the 50M of data is being processed), will BAD THINGS happen?
Thank you for your help. If this is a question that might be better posted to another forum, that advice would be welcomed also.
-Mike Kemp

 

[harryrich]

My suggestion is that it is much easier to periodically compact the MDB files. I've been putting this in a batch file that runs at night. Check out the access command line parmeters in you help. I think the keyword that will get you there is "startup".

I think it is something like MSACCESS /COMPACT filename.

You can speed up the process by putting all the temporaries in a separate back end database. If all the data is deleted this database should compact in a few seconds. Obviously, Make Table Queries won't work on a back end.

I seriously doubt that you are running MS Access in 3% of 128 megabytes. If you are dealing with large tables or complex queries, 50 megabytes in memory should hurt your performance.

Best,

Harry

 

[MichaelRed]

I frequently use memory arrays and UDTs to do intermediate calculations. It does - EVENTUALLY - impact performance, however I have not seen this occur very often.

An alternative approach is to do the intermediate calcs in unnamed queries, as these are not saved as part of
Ms. Access, and thus do not make the db grow.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over