Not enough memory for an array

[cainemart]

I am trying to re-work someone elses code because we have a problem with 2 arrays in the code.

EPArray(Stateindex, Polindex, EffYear, EffMonth, PolYr, PolMth, CompanyIndex, CovgIndex)

This is just to show you how bid this array gets. It loops through about 1 million(this can be as big as 4 mill) transactions collects that information for all of the transactions. So this mother is big. Well it is getting so big that it usually locks up the system because of the lack of memory.


My question is would it be better to put this info into a table for processing vs the array?

What can I do about the memory problem if not?

All advice or comments will be greatly appreciated!

 

[jimmythegeek]

I think your memory problem is a secondary consideration. 1 - 4 million records is really pushing Access beyond it's limits, especially if the table(s) are very wide (many fields).

You need to consider moving to a larger DBMS (SQL Server, Oracle) if you are going to be dealing with this type of record counts.

And yes, put the data in a table for any analysis, or manipulation, although even a table won't help that much, as if you have to do anything with a million records you will be creeping.

We had a table that had about 1.5 million records with 8 fields, and it took minutes rather than seconds to do anything with it, especially a non-indexed field. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?