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

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

reusing a recordset for a pivot-table?
 

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