B
bartwarnez
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
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 hedoes 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