MS Excel 2003: Partially Refresh PivotCache or Combine PivotCache

Question

I have an Excel pivotcache that is connected to an Access database query. However, if I change the source data in Access (e.g. change a value, add / delete a record), I have to refresh the pivotcache which in turn runs the query again to retrieve all records on the entire dataset. This is very inefficient. I only want to retrieve records that change frequently (ideally, only records that change, but that will be a future consideration).

I have tried the solutions below, but they are not suitable for my purpose:
* Create two query tables in Excel in the same worksheet, and create a pivot table on the entire worksheet containing the two query tables. I only refresh the "current" query table. However, I am limited to 65536 rows which is less than the recordcount in my query.
* Create two pivot tables. However, this is too much effort on the user having to set up pivot tables twice. I want the optimisation happen behind the scenes rather than the user having to change their habits.

The potential solution I am considering now is to refresh the pivotcache using two ADO recordsets, one for historical data, and the second for current data that changes frequently. If I change the current data, I only run the query for the current dataset.

However, I seem to have trouble converting a pivotcache to an ADO recordset. The line "pvtRecordset.MoveFirst" throws an error. It is there for test purposes. If that line does not work, then I cannot combine a new recordset with a pivotcache using the rsCombineRecordsets function.

The other way is to convert an ADO recordset to a pivotcache (i.e. set pivotcache.recordset = ADOrecordset). The ADO recordset on historical data is saved into memory, so we only need to open the recordset once. However, I have no idea how to get this to work either. The pivot table data remains unchanged as it still shows the results of "strSqlHist" rather than the combination of "strSqlHist" and "strSqlCurr".

Sub Main()
    RefreshPivotCache "CURRENT"
End Sub

Public Function RefreshPivotCache(strRefreshCmd As String)
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim pvtCache As PivotCache
    Dim pvtRecordset As ADODB.Recordset
    Dim ptt As PivotTable
    Dim strSqlHist As String, strSqlCurr As String
    Dim strCon As String
    Dim rstCollection As New Collection

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=I:\Cash Management\Cash_M.mdb;"

    strSqlHist = "SELECT * FROM v_CFMT WHERE Trx_Date < DateValue('01-DEC-2009')"
    strSqlCurr = "SELECT * FROM v_CFMT WHERE Trx_Date >= DateValue('01-DEC-2009')"

    If strRefreshCmd = "NEW" Then

        'Open the connection and fill the Recordset.
        cnn.Open strCon
        Set rst = cnn.Execute(strSqlHist)

        'Add pivot cache and assign the cache source to the recordset
        Set pvtCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        Set pvtCache.Recordset = rst

        'Create pivot table and assign to pivot cache
        Set ptt = pvtCache.CreatePivotTable(TableDestination:=ActiveCell, TableName:="PT_ADO")

    ElseIf strRefreshCmd = "CURRENT" Then
        'Open the connection and fill the Recordset.
        cnn.Open strCon
        Set rst = cnn.Execute(strSqlCurr)

        'Convert pivotcache to recordset - does not work
        Set pvtRecordset = ActiveCell.PivotTable.PivotCache.Recordset
        pvtRecordset.MoveFirst 'Operation is not allowed when the object is closed

        'Combine the two recordsets and assign to the pivotcache recordset
        rstCollection.Add pvtRecordset
        rstCollection.Add rst
        Set pvtRecordset = rsCombineRecordsets(rstCollection) 'custom function.

    End If

    'Release objects from memory
    cnn.Close
    Set cnn = Nothing
    If CBool(rst.State And adStateOpen) Then rst.Close
    Set rst = Nothing
    Set ptt = Nothing
    Set rstCollection = Nothing
End Function

In sum, how can I partially refresh a pivotcache or combine two pivotcaches?

Answer 1

Are you tring to get a disconnected recordset when you say "store a recordset into memory"? see how to create a disconnected recordset