ExcelBanter - View Single Post - reusing a recordset for a pivot-table

Bart op de grote markt

wrote:
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

anyone?