Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reusing a recordset for a pivot-table?
Hello,
I want to be able to use a recordset that I get through ADO three times, but it doesn't work like I want it to. The obvious reason is that I don't want to call the same procedure 3 times, becoz then it takes 3 times as long of course. Reusing the recordset means a 3 times faster report. I added the code below. First I build the report with the recordset. There my procedure-call is executed as you can see. (objRs01 is my recordset-object). Ok that works fine! Becoz I want to reuse that recordset I put objRs01.moveFirst at the end (don't really know if that's necessary, but it doesn't do any harm I think). After that I want to make my first pivot table. (No I don't want to define a range in my first report to create the pivot from that; insetad I want to use my objRs01-recordset). Ok that works fine! Now I want to use again the same recordset for a third time. This time for a second pivot-table. But :( :( :( this does not work. I think he does get the columns but no data or smth like that. As u see in the code, I put objRs01.Requery and as a result, the second pivot works fine! BUT I don't want a requery becoz it takes time. I want to use the recordset that I already had, not a refreshed one... Why does it work for the first Pivot and not for the 2nd? And how can I make the 2nd work too (without executing that requery...)? Any help would be greatly appreciated! Bart Private Sub BuildReport1() (...) With objRs01 .Open "call Reports_get_results(" & report_number & ",'" & period_id & "','" & language_id & "','" & division_id & "')" If Not objRs01 Is Nothing And .State = adStateOpen And Not .EOF Then .MoveFirst Do Until .EOF (...) datacounter = datacounter + 1 .MoveNext Loop End If .MoveFirst End With End Sub ---------------------------------------------------------------------------*---------------------------------------------------------------- Private Sub create_pivot1() (...) With wsSheet Set rnStart = Worksheets("PIVOT").Range("C8") End With Set ptCache2 = ThisWorkbook.PivotCaches.Add(SourceType:=xlExterna l) 'Add the Recordset as the source to the pivotcache. With ptCache2 .RefreshOnFileOpen = True Set .Recordset = objRs01 End With 'Create the pivottable Set ptTable = ptCache2.CreatePivotTable(TableDestination:=rnStar t, _ TableName:="RESULTS") 'Set up the pivottable. With ptTable .SmallGrid = False .AddFields RowFields:=Array("Category", "GROUP_id", "CHAIN", "Data") (...) End With (...) End Sub ---------------------------------------------------------------------------*---------------------------------------------------------------- Private Sub create_pivot1B() (...) objRs01.Requery objRs01.MoveFirst (...) With wsSheet Set rnStart = Worksheets("PIVOT2").Range("C8") End With Set ptCache2 = ThisWorkbook.PivotCaches.Add(SourceType:=xlExterna l) 'Add the Recordset as the source to the pivotcache. With ptCache2 .RefreshOnFileOpen = True Set .Recordset = objRs01 End With 'Create the pivottable Set ptTable = ptCache2.CreatePivotTable(TableDestination:=rnStar t, _ TableName:="RESULTS2") 'Set up the pivottable. With ptTable (...) End With (...) objRs01.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reusing a recordset for a pivot-table?
I would get the recordset in an array and do all the further
manipulations on that array. This goes with arr = rs.GetRows If you want you can declare the array Public so it won't go out of scope. Apart from saving you having to run the query 3 times it will speed things up even further as array manipulations are faster than recordset manipulations and GetRows runs very fast. RBS "Bart op de grote markt" wrote in message oups.com... Hello, I want to be able to use a recordset that I get through ADO three times, but it doesn't work like I want it to. The obvious reason is that I don't want to call the same procedure 3 times, becoz then it takes 3 times as long of course. Reusing the recordset means a 3 times faster report. I added the code below. First I build the report with the recordset. There my procedure-call is executed as you can see. (objRs01 is my recordset-object). Ok that works fine! Becoz I want to reuse that recordset I put objRs01.moveFirst at the end (don't really know if that's necessary, but it doesn't do any harm I think). After that I want to make my first pivot table. (No I don't want to define a range in my first report to create the pivot from that; insetad I want to use my objRs01-recordset). Ok that works fine! Now I want to use again the same recordset for a third time. This time for a second pivot-table. But :( :( :( this does not work. I think he does get the columns but no data or smth like that. As u see in the code, I put objRs01.Requery and as a result, the second pivot works fine! BUT I don't want a requery becoz it takes time. I want to use the recordset that I already had, not a refreshed one... Why does it work for the first Pivot and not for the 2nd? And how can I make the 2nd work too (without executing that requery...)? Any help would be greatly appreciated! Bart Private Sub BuildReport1() (...) With objRs01 .Open "call Reports_get_results(" & report_number & ",'" & period_id & "','" & language_id & "','" & division_id & "')" If Not objRs01 Is Nothing And .State = adStateOpen And Not .EOF Then .MoveFirst Do Until .EOF (...) datacounter = datacounter + 1 .MoveNext Loop End If .MoveFirst End With End Sub ---------------------------------------------------------------------------*---------------------------------------------------------------- Private Sub create_pivot1() (...) With wsSheet Set rnStart = Worksheets("PIVOT").Range("C8") End With Set ptCache2 = ThisWorkbook.PivotCaches.Add(SourceType:=xlExterna l) 'Add the Recordset as the source to the pivotcache. With ptCache2 .RefreshOnFileOpen = True Set .Recordset = objRs01 End With 'Create the pivottable Set ptTable = ptCache2.CreatePivotTable(TableDestination:=rnStar t, _ TableName:="RESULTS") 'Set up the pivottable. With ptTable .SmallGrid = False .AddFields RowFields:=Array("Category", "GROUP_id", "CHAIN", "Data") (...) End With (...) End Sub ---------------------------------------------------------------------------*---------------------------------------------------------------- Private Sub create_pivot1B() (...) objRs01.Requery objRs01.MoveFirst (...) With wsSheet Set rnStart = Worksheets("PIVOT2").Range("C8") End With Set ptCache2 = ThisWorkbook.PivotCaches.Add(SourceType:=xlExterna l) 'Add the Recordset as the source to the pivotcache. With ptCache2 .RefreshOnFileOpen = True Set .Recordset = objRs01 End With 'Create the pivottable Set ptTable = ptCache2.CreatePivotTable(TableDestination:=rnStar t, _ TableName:="RESULTS2") 'Set up the pivottable. With ptTable (...) End With (...) objRs01.Close End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reusing a recordset for a pivot-table?
Thx for the hints! Speed is a big issue so I will try that now :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
pivot table from recordset - very slow performance | Charts and Charting in Excel | |||
reusing a recordset for a pivot-table | Excel Programming | |||
Using WSH to populate a pivot cache with an ADO recordset | Excel Programming | |||
Error opening recordset where table is empty (but has field names) - How do I avoid? | Excel Programming |