Hello -- I am going to use VBA to pull a multitude of elements (several hundred) from a pivot table whose data source is a 40meg table in Access. To get the data I will need to re-form the pivot in various ways (probably a hundred.)

Can anyone give me some insight as to whether it would be more efficient to load a pivot cache within Excel and do calcs there (possibly in an array), OR reach into Access via ADO and do the calcs there?

Thanks for your help!

jwk

It all depends on the calculation/filter you want to do

If it can be done using some queries you can do that using ADO as the returned result is limited to that critieria

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Thanks for your response; good information.

I will mostly be pulling simple data elements and doing a few simple calculations with these pulled elements. The filters would be maybe five dimensional: Market by Type by Month by Dept by ID, for example.

My question is: is it quicker/more efficient to manipulate a 40M pivot table a couple hundred times in Excel or to do a few hundred relatively simple SQL queries via ADO?

Thanks again --