reusing a recordset for a pivot-table?

[Bart op de grote markt]

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:=xlExternal)



'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:=rnStart,
_
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:=xlExternal)



'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:=rnStart,
_
TableName:="RESULTS2")


'Set up the pivottable.
With ptTable
(...)
End With


(...)
objRs01.Close


End Sub

 

[RB Smissaert]

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


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:=xlExternal)



'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:=rnStart,
_
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:=xlExternal)



'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:=rnStart,
_
TableName:="RESULTS2")


'Set up the pivottable.
With ptTable
(...)
End With


(...)
objRs01.Close


End Sub

 

[Bart op de grote markt]

Thx for the hints! Speed is a big issue so I will try that now