Pull data Access to Excel Pivot Table using an ADODB Connection

[Harry Flashman]

I have some code that successfully pulls data from Access and puts it into a pivot table. I have been looking around at similar routines and I have found some that use a PivotCache property that I have thus far not used, namely .OptimizeCache.

For example:

Set ptCache = wbBook.PivotCaches.Add(SourceType:=xlExternal)
     
     'Add the Recordset as the source to the pivotcache.
    With ptCache
        .OptimizeCache = True
        Set .Recordset = rst
    End With

For the full routine see this link to a post on Ozgrid: Populate pivottables using ADO

The full routine creates fetches the data from Access, creates the pivot table and then populates the Pivot Table (or rather the PivotCache).

When I run this code, however I get an error, Application defined or object defined error, right on the line .OptimizeCache = True

Why might this be?

For a look at a routine that is a bit closer to the one I intend to modify using this OptimizeCache property see this thread I posted a little while back:

http://www.mrexcel.com/forum/excel-...-access-excel-pivot-table-speed-up-query.html

I have no trouble use an ADODB Connection otherwise, and to be honest I am not completely sure what .OptimizeCache actually does, but it sounds like it might either improve pivot table performance, or make it easier and quicker to refresh the pivot table when new data arrives.

Is anyone familar with this PivotCache property, .OptimizeCache?

Is it worth using? Where might I be going wrong.

I am using Excel 2013 (64 bit) Access 2010 (64 bit). As I mentioned I am currently successfully fetching data using an ADODB Connection, but this one point I am getting an error message.

I am going to hazard a guess that my issue might be something to do with using a 64 bit version of Office (simply because this seems to be the most frequent cause of problems). But I would like to know for sure.