none
Reuse a single ADODB.Recordset to create several Pivot Caches RRS feed

  • Question

  • Hi All,

    I have the following problem.
    I create an ADODB.Recordset and fill it with data. Then, I create a new workbook within which I create a pivot cache with the recordset as follows:

    Set oPivotCache = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
    Set oPivotCache.Recordset = rsReportData

    Then, using the pivot cache, I create a pivot table. Everything is fine until I want to use the same recordset to create a new pivot table in a new workbook. I call the same procedure (CreateReports) from the Main procedure and pass a reference to the same recordset. I get absolutely no errors when creating the second pivot cache or even a pivot table but the problem is there is absolutely no data in the new pivot table despite the fact that all pivot fields are created correctly. I tried calling rsReportData.MoveFirst before calling the CreateReports for the second time – no difference. Furthermore, if I call rsReportDataSet.RecordCount before the second cache is created I’m getting the correct number of records. What am I missing/doing wrong?
    The two main procedures are as follows:

    Private Sub Main()
      Dim rsReportData                    As ADODB.Recordset
        Set rsReportData = New Recordset
        Set rsReportData = GetReportData(vbNullString, sScenarioVersion, dteDateFrom, dteDateTo)
        
        ' Create reports
        sFileName = "CR_Volume"
        Call CreateReports(sTempFolderPath, sFileName, dteDateFrom, dteDateTo, 1, rsReportData)
        
        sFileName = "CR_Prices"
        Call CreateReports(sTempFolderPath, sFileName, dteDateFrom, dteDateTo, 2, rsReportData)
    End Sub

    Private Sub CreateReports(ByVal sPath As String, ByVal sFileName As String, ByVal dteDateFrom As Date, ByVal dteDateTo As Date, ByVal iReportPackage As Integer, ByRef rsReportData As ADODB.Recordset)
    
        Dim wkbReport                           As Excel.Workbook
        Dim wksReport                           As Excel.Worksheet
        Dim oPivotCache                         As Excel.PivotCache
        Dim sReportTitle                        As String
    
        Set wkbReport = Workbooks.Add
        Set wksReport = wkbReport.Worksheets(1)
        
        Select Case iReportPackage
        Case 1
            If Not rsReportData.State = adStateClosed Then
                sReportTitle = "FirstReport"
                Set wksReport = wkbReport.Worksheets.Add(After:=wkbReport.Worksheets(wkbReport.Worksheets.Count))
                wksReport.Name = sReportTitle
                Set oPivotCache = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
                Set oPivotCache.Recordset = rsReportData
                Call CreateReportTable (wksReport, "FirstReport", oPivotCache)
            End If 
        Case 2
            If Not rsReportData.State = adStateClosed Then
                sReportTitle = "SecondReport"
                Set wksReport = wkbReport.Worksheets.Add(After:=wkbReport.Worksheets(wkbReport.Worksheets.Count))
                wksReport.Name = sReportTitle
                Set oPivotCache = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
                Set oPivotCache.Recordset = rsReportData
                Call CreateReportTable (wksReport, "SecondReport", oPivotCache)
            End If 
    End Select
           
        wkbReport.SaveAs Filename:=sPath & "\" & sFileName, FileFormat:=xlWorkbookDefault, ReadOnlyRecommended:=True
        wkbReport.Close
    End Sub


    -- Igor M.






    • Edited by Igor-M Thursday, July 30, 2015 12:14 PM
    Thursday, July 30, 2015 11:50 AM

Answers

  • Your code will work as expected if you create a separate instance of Microsoft Excel, and then use that instance to build the workbooks, pivot caches, and pivot tables.

    Try adding these lines to the CreateADOCacheTest procedure:

    Dim appExcel As Excel.Application

    Set appExcel = New Excel.Application
    appExcel.Visible = True

    Then use the modified line:
    Set wkbReport = appExcel.Workbooks.Add


    Finally, when cleaning up, use these lines to destroy the appExcel object:
    If Not appExcel Is Nothing Then
        appExcel.Quit
        Set appExcel = Nothing
    End If




    • Edited by Mark18991230 Monday, August 3, 2015 7:54 PM
    • Marked as answer by Igor-M Thursday, August 6, 2015 6:19 AM
    Monday, August 3, 2015 6:54 PM

All replies

  • Hi,

    after some further testing I have decided to simplify the question/code a bit. So basically, the question now is why with the following code the second pivot table/cache has no data and what can be done to have it populated with data. It should be pointed out that the second pivot table is created successfully and has all the fields that the first pivot and the recordset have but no data.

    Private Sub CreateADOCacheTest(ByRef rsReportData As ADODB.Recordset)
        
        Dim oPivotCache1                        As Excel.PivotCache
        Dim oPivotCache2                        As Excel.PivotCache
        Dim oPivotTable1                        As Excel.PivotTable
        Dim oPivotTable2                        As Excel.PivotTable
        Dim wkbReport                           As Excel.Workbook
        Dim wksReport1                          As Excel.Worksheet
        Dim wksReport2                          As Excel.Worksheet
        
        Set wkbReport = Application.Workbooks.Add
        Set wksReport1 = wkbReport.Worksheets.Add(After:=wkbReport.Worksheets(wkbReport.Worksheets.Count))
        Set wksReport2 = wkbReport.Worksheets.Add(After:=wkbReport.Worksheets(wkbReport.Worksheets.Count))
        Set oPivotCache1 = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
        Set oPivotCache1.Recordset = rsReportData
        Set oPivotCache2 = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
        Set oPivotCache2.Recordset = rsReportData
        
        Set oPivotTable1 = oPivotCache1.CreatePivotTable(TableDestination:=wksReport1.Range("A8"), TableName:="Rep1", ReadData:=True, DefaultVersion:=xlPivotTableVersion12)
        Set oPivotTable2 = oPivotCache2.CreatePivotTable(TableDestination:=wksReport2.Range("A8"), TableName:="Rep2", ReadData:=True, DefaultVersion:=xlPivotTableVersion12)
        
        On Error Resume Next
        Debug.Print "Recordset recordcount: " & rsReportData.RecordCount
        Debug.Print "oPivotCache1 recordcount: " & oPivotCache1.RecordCount
        Debug.Print "oPivotCache2 recordcount: " & oPivotCache2.RecordCount
    End Sub

    Recordset recordcount: 2000
    oPivotCache1 recordcount: 2000
    oPivotCache2 recordcount: 0


    -- Igor M.

    Thursday, July 30, 2015 8:49 PM
  • Hi IgorMI5,

    Base on my test in excel 2013, I can’t create the second PivotTable with your code, it will throws the exception in oPivotCache2.CreatePivotTable line.

    Could you share a sample file on the OneDrive?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 31, 2015 7:18 AM
    Moderator
  • Hi Starain,

    here is a fully functional code (works in XL2007). Just remember to add a reference to Microsoft ActiveX Data Objects Library.

    Option Explicit
    
    Private Sub CreateADORecordset()
        Dim rsReportData            As ADODB.Recordset
        
        Set rsReportData = New ADODB.Recordset
        
        With rsReportData
            .Fields.Append "Make", adVarWChar, 50, adFldMayBeNull
            .Fields.Append "Model", adVarWChar, 50, adFldMayBeNull
            .Fields.Append "Value", adDouble
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .LockType = adLockBatchOptimistic
            .Open
        End With
        
        With rsReportData
            .AddNew ("Make"), "Ford"
            .Fields("Model").Value = "Focus"
            .Fields("Value").Value = 25009.25
            .AddNew ("Make"), "Ford"
            .Fields("Model").Value = "Mondeo"
            .Fields("Value").Value = 33210.83
            .AddNew ("Make"), "Audi"
            .Fields("Model").Value = "TT"
            .Fields("Value").Value = 99210.09
            .UpdateBatch
        End With
        
        Call CreateADOCacheTest(rsReportData)
    
    End Sub
    
    Private Sub CreateADOCacheTest(ByRef rsReportData As ADODB.Recordset)
        
        Dim oPivotCache1                        As Excel.PivotCache
        Dim oPivotCache2                        As Excel.PivotCache
        Dim oPivotTable1                        As Excel.PivotTable
        Dim oPivotTable2                        As Excel.PivotTable
        Dim wkbReport                           As Excel.Workbook
        Dim wksReport1                          As Excel.Worksheet
        Dim wksReport2                          As Excel.Worksheet
        
        Set wkbReport = Application.Workbooks.Add
        Set wksReport1 = wkbReport.Worksheets(1)
        Set oPivotCache1 = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
        Set oPivotCache1.Recordset = rsReportData
        Set oPivotCache2 = wkbReport.PivotCaches.Create(SourceType:=xlExternal)
        Set oPivotCache2.Recordset = rsReportData
        
        Set oPivotTable1 = oPivotCache1.CreatePivotTable(TableDestination:=wksReport1.Range("A8"), TableName:="Rep1", ReadData:=True, DefaultVersion:=xlPivotTableVersion12)
        Set oPivotTable2 = oPivotCache2.CreatePivotTable(TableDestination:=wksReport1.Range("F8"), TableName:="Rep2", ReadData:=True, DefaultVersion:=xlPivotTableVersion12)
        
        With oPivotTable1
            .AddFields RowFields:=Array("Make", "Model")
            .AddDataField .PivotFields("Value"), , xlSum
        End With
        With oPivotTable2
            .AddFields RowFields:=Array("Make", "Model")
            .AddDataField .PivotFields("Value"), , xlSum
        End With
        
        wksReport1.Range("A1").Value2 = "Recordset recordcount: " & rsReportData.RecordCount
        wksReport1.Range("A2").Value2 = "No. of pivot caches: " & wkbReport.PivotCaches.Count
        wksReport1.Range("A3").Value2 = "oPivotCache1 recordcount: " & oPivotCache1.RecordCount
        wksReport1.Range("A4").Value2 = "oPivotCache2 recordcount: " & oPivotCache2.RecordCount
    End Sub
    


    -- Igor M.

    Friday, July 31, 2015 10:27 AM
  • Hi IgorMI5,

    For the same workbook, we could create PivotTable by using the same PivotCache.

    For the different workbook, I have the same result with you, I check the properties by adding watch, it has the value in the watch window, but the result is different.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 3, 2015 2:17 AM
    Moderator
  • Hi Starain,

    thanks for the reply. What I'm trying to achieve is to create two (or more) pivot tables using the same source data but I'd like the pivot tables to be independent (i.e. created with different pivot caches). At the moment I have to destroy the recordset and retrieve the data set again from the data base which is very time consuming. And because I need to use different grouping on the data the pivot tables cannot be based on the same pivot cache.
    And do I understand correctly that you managed to create two pivot tables with the same pivot cache and the second pivot table contained data? I'm asking because based on my experience there is no difference in whether the pivot table is created in the same or a completely new workbook - every second and consecutive pivot table/cache created with the same recordset has no data.

    Kind regards,


    -- Igor M.


    • Edited by Igor-M Monday, August 3, 2015 11:02 AM
    Monday, August 3, 2015 9:12 AM
  • Your code will work as expected if you create a separate instance of Microsoft Excel, and then use that instance to build the workbooks, pivot caches, and pivot tables.

    Try adding these lines to the CreateADOCacheTest procedure:

    Dim appExcel As Excel.Application

    Set appExcel = New Excel.Application
    appExcel.Visible = True

    Then use the modified line:
    Set wkbReport = appExcel.Workbooks.Add


    Finally, when cleaning up, use these lines to destroy the appExcel object:
    If Not appExcel Is Nothing Then
        appExcel.Quit
        Set appExcel = Nothing
    End If




    • Edited by Mark18991230 Monday, August 3, 2015 7:54 PM
    • Marked as answer by Igor-M Thursday, August 6, 2015 6:19 AM
    Monday, August 3, 2015 6:54 PM