I'm having performance issues with Excel Pivot Tables connected to a large Access table.
I manage two Access databases of ledger details that are used by 15 people both in the US and India. The first database processes the data (Extract, Transform, Load), and then outputs a final table to a second standalone database for users to connect to with Excel Pivot Tables over the network. The final table currently has 508,000 records with 42 columns, and with about 50,000 records added each month, I have 2 more months to go in this fiscal year (3/31 year end).
When the Pivot Table connects to the data, it scans the entire table and loads it into the Pivot Cache. Looking at the memory usage for the Excel Instance in Task Manager, it is exceeding a whopping 1.6 gigabytes.
To reduce the size of the Pivot Cache, i'm considering:
1. Creating queries based on the Output Table so users can connect to smaller data sets, or
2. Allow users to choose dimension elements in either a Form, List Box, or Combo Box that would then use VBA to limit the data returned to the Pivot Cache.
How should I handle it?