Multiple pivot tables to connect to same (Access) source - refresh takes >50x longer in 2007 than in 2010

[xls_mycroft]

Hi,

I have a datasource with some 140,000 rows in Access. In Excel I have a total of 17 pivot tables, all using the same connection, to run multiple analyses on the dataset.

If I update the connection in Excel 2010 (with Access 2010), the update runs once and takes 8 seconds - so far so good. However, in Office 2007, the update process takes a whopping 450 seconds! It seems like the update is done 17 times - and that every update takes much long (on average 26seconds).

Did anybody encounter this issue before? Any idea on how to solve/circumvent the issue?

Thanks!

 

[xls_mycroft]

Okay, I found a workaround: If I download all the data into one Excel table and use this Excel table as the source for all 17 pivots, it only downloads the data once and refreshes the pivot table cache once. While not ideal (as now the workbook contains a copy of the data and is therefore immensely blown up in size), I guess this has to work for the time being...

Hi,

I have a datasource with some 140,000 rows in Access. In Excel I have a total of 17 pivot tables, all using the same connection, to run multiple analyses on the dataset.

If I update the connection in Excel 2010 (with Access 2010), the update runs once and takes 8 seconds - so far so good. However, in Office 2007, the update process takes a whopping 450 seconds! It seems like the update is done 17 times - and that every update takes much long (on average 26seconds).

Did anybody encounter this issue before? Any idea on how to solve/circumvent the issue?

Thanks!