Change pivot cache data source

[Jon von der Heyden]

Hi All

I have a workbook that contains several macros. One slices and dices data from another workbook. The next one will upload that table to an access database. Then another macro to pass parameters to a query in that access database.

The workbook then contains several pivot tables from that very same query table. Works a treat!

I would like to make it a little more bullet proof by adding in functionaility to change where the pivot cache collects its' data from. I still want it to read that database and that very same query, but I would like to be able to change the location of the access database just in case someone moves it to a different directory. So now the directory is C:\Workflow.mdb, but what happends if it moves to H:\Workflow.mdb.

The workbook only has the one pivot cache so I am wondering if I can change its source location in one hit without having to pass the entire SQL string through again.

Can anyone give me some suggestions please?

 

[RoryA]

If you have multiple pivots based off one cache, it's very difficult to change that cache directly as you can if only one table is based off it. What you can do is create a new cache, then change the pivottables to point at that new cache. (You can assign the commandtext from one cache to the other)

 

[Jon von der Heyden]

Rory

Can you give me an idea for the syntax to point an existing pivot table to a cache? I like this way cos I don't have to build all the pivot tables on the fly and should be able to refresh the whole lot.

Thanks!

 

[RoryA]

You just set the CacheIndex property of the table to the relevant number. (you can use the Index property of the cache)

 

[Jon von der Heyden]

Rory I'm stuck

I have what I think is correctly building a cache but when I test I still only count 1 cache and I imagine that it is the inital one. This bit (below) runs without error but I can't see what its index is and therefore don't know how to point my other pivot tables to it.

Also, what happens to the old cache? Can I kill it?

Option Explicit

Sub NewPivotCache()
 
    Dim recData As ADODB.Recordset
    Dim conDatabase As New ADODB.Connection
    Dim strAccessPath As String, strConnection As String, strSQL As String
    Dim pvcNew As PivotCache
    Dim PT As PivotTable
    
    strAccessPath = shtControl.Range("rngDbLocation").Text
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strAccessPath & ";"
    
    conDatabase.Open strConnection
    
    strSQL = "SELECT PivotData_qry.* " & _
                "FROM PivotData_qry;"
                
    Set recData = New ADODB.Recordset
    Set recData.ActiveConnection = conDatabase
    recData.Open strSQL
    
    Set pvcNew = ThisWorkbook.PivotCaches.Add(xlExternal)
    Set pvcNew.Recordset = recData
    
    'shtTrend.PivotTables(1).CacheIndex = NEEDS TO REFERENCE THIS NEW CACHE
    
    recData.Close
    Set recData = Nothing
    Set pvcNew = Nothing
End Sub

 

[RoryA]

Hmm, yeah it seems you need to actually create a pivot table from the cache first. Try this:

Sub NewPivotCache()
 
    Dim recData As ADODB.Recordset
    Dim conDatabase As New ADODB.Connection
    Dim strAccessPath As String, strConnection As String, strSQL As String
    Dim pvcNew As PivotCache
    Dim PT As PivotTable
    Dim wks As Worksheet
    
    strAccessPath = shtControl.Range("rngDbLocation").Text
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strAccessPath & ";"
    
    conDatabase.Open strConnection
    
    strSQL = "SELECT PivotData_qry.* " & _
                "FROM PivotData_qry;"
                
    Set recData = New ADODB.Recordset
    Set recData.ActiveConnection = conDatabase
    recData.Open strSQL
    
    Set pvcNew = ThisWorkbook.PivotCaches.Add(xlExternal)
    Set pvcNew.Recordset = recData
    Set wks = Sheets.Add
    pvcNew.CreatePivotTable wks.Range("A3")
    shtTrend.PivotTables(1).CacheIndex = pvcNew.Index
    Application.displayalerts = false
    wks.Delete
    Application.displayalerts = True
    recData.Close
    Set recData = Nothing
    Set pvcNew = Nothing
End Sub

 

[Jon von der Heyden]

I think it is bizarre that we have to create the pivot table but I don't care because it works. Thanks Rory. And it seems that the old cache has been removed automatically so another bonus!
Thanks again

 

[RoryA]

I think it is bizarre that we have to create the pivot table

Me too, though it may be that I'm just doing it wrong!
I think 2007 is easier for this, though I haven't actually tried it yet.

 

[Jon von der Heyden]

Unfortunately this lot won't upgrade to 2007 and will go directly to 2010; and by that time I will be long gone.

 

[RoryA]

Unfortunately this lot won't upgrade to 2007 and will go directly to 2010

That's not unfortunate, that's sensible!