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 =3D adStateOpen And Not .EOF

Then
.MoveFirst
Do Until .EOF
(...)
datacounter =3D datacounter + 1
.MoveNext
Loop
End If
.MoveFirst
End With
End Sub
---------------------------------------------------------------------------=
=AD----------------------------------------------------------------

Private Sub create_pivot1()
(=2E..)


With wsSheet
Set rnStart =3D Worksheets("PIVOT").Range("C8")
End With
Set ptCache2 =3D ThisWorkbook.PivotCaches.Add(SourceType:=3DxlExternal)



'Add the Recordset as the source to the pivotcache.
With ptCache2
.RefreshOnFileOpen =3D True
Set .Recordset =3D objRs01
End With


'Create the pivottable
Set ptTable =3D ptCache2.CreatePivotTable(TableDestination:=3DrnStart,
_
TableName:=3D"RESULTS")


'Set up the pivottable.
With ptTable
.SmallGrid =3D False
.AddFields RowFields:=3DArray("Category", "GROUP_id", "CHAIN",
"Data")
(...)
End With
(=2E..)
End Sub
---------------------------------------------------------------------------=
=AD----------------------------------------------------------------

Private Sub create_pivot1B()
(=2E..)


objRs01.Requery
objRs01.MoveFirst


(=2E..)


With wsSheet
Set rnStart =3D Worksheets("PIVOT2").Range("C8")
End With
Set ptCache2 =3D ThisWorkbook.PivotCaches.Add(SourceType:=3DxlExternal)



'Add the Recordset as the source to the pivotcache.


With ptCache2
.RefreshOnFileOpen =3D True
Set .Recordset =3D objRs01
End With


'Create the pivottable
Set ptTable =3D ptCache2.CreatePivotTable(TableDestination:=3DrnStart,
_
TableName:=3D"RESULTS2")


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


(...) =20
objRs01.Close=20


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


"Bart op de grote markt" <[email protected]> wrote in message
news:[email protected]...
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