Hi,
(cross-posted from MrExcel due to no response at all since April 25th)
I have a table in Excel that links to a table in Access which is taking a very long time (>45mins) to refresh.
Table size:
252 fields/columns, 11,000+ rows/records of various data types in a single table. There are no additional calculated fields in the table.

Pivots/PivotCache using the table:
There are 154 pivots all sharing a single PivotCache' All are set to not 'Save Source data with file', but to 'Enable show details' and to 'Refresh data when opening file' (nb. this last doesn't seem to add much of a delay). The single Pivotcache is reported as using 2,8472,688 'MemoryUsed' and has 11151 records when I check on this occasion. It uses all 252 columns

I have this automated (as part of an overall process of checking the connection, refreshing everything and setting the pivots) but even if I just do the Refresh connection step 'by hand', ie by simply rh-clicking on the table and 'Refresh', it's just the same at approx 45mins. I have since discovered that it takes even longer on a PC that only has 8GB (mine has 12GB).

One thing I tried was to delete all of the sheets containing Pivots from the workbook and Refresh the table - the result was about 4 minutes, not quick as such but considerably better.

Any thoughts? Is it possible to resize or disconnect the the PivotCache whilst refreshing the table it's based on perhaps?

Connection details if it's of an use:
(OLEDBConnection)
.BackgroundQuery = False
.CommandText = Array(TableName)
.CommandType = xlCmdTable
.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & dbPathAndName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = dbPathAndName
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False

thanks
Paul