Need code assistance to update pivot table from an ADO recordset
 

I have code that works fine in accessing data from a MS Access database via
an ADODB connection and creating a pivot table. My issue is that once
created, I am not able to refresh the pivot table without recreating it. In
other words, I have the data in a recordset but can't get it into an existing
pivot table. Please provide some coding assistance.

Code Snippet
Sub ADO_PT_Refresh()
'Dimension Variables
Dim strDB_Name As String
Dim strDB_Location As String
Dim strDB_TableName As String
Dim strSQL As String
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRS As ADODB.Recordset


'Set Variable
strDB_Name = Range("db_name").Value
strDB_Location = Range("db_location").Value
txtAccessFile = strDB_Location + strDB_Name

' Create the SQL & Command
strDB_TableName = Range("db_Query").Value
strSQL = "Select * FROM " + strDB_TableName + " ;"

'Open Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & txtAccessFile & ";" & _
"Persist Security Info=False"
objConn.Open
Set objRS = objConn.Execute(strSQL)

' Create a PivotTable cache
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
Set objPivotCache.Recordset = objRS


'***********************
'
' Need code to take the Recordset objRS and updated the existing Pivot
Table
' ActiveSheet.PivotTables("Test PT")
'
'***********************




' Close the database.
objConn.Close
Set objConn = Nothing

End Sub

Need code assistance to update pivot table from an ADO recordset
 

Maybe I misunderstood the question, but it looks to me like you are trying to
recreate the pivot cache each time you want to refresh the data. If I
understand this correctly, you should not do that, you should simply refresh
the pivot table with code similar to:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

Of course you would need to identify your specific pivot table. Remember
that if more than one pivot table is attached to a pivot cache, then all
tables will be refreshed when you refresh one.

It is a crazy world we live in.

Need code assistance to update pivot table from an ADO records
 

I'm not looking to recreate the pivot table each and everytime, but merely
take the recordset "objRS" and update the pivotcache on
ActiveSheet.PivotTables("Test PT").

If I got to the exisitng pivottable and do a right click, the Refresh Data!
option is grayed out. I assume that this is because the orginal pivot table
was created from ADO recordset, i.e., now disconnected.

"TomPl" wrote:

Maybe I misunderstood the question, but it looks to me like you are trying to
recreate the pivot cache each time you want to refresh the data. If I
understand this correctly, you should not do that, you should simply refresh
the pivot table with code similar to:

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

Of course you would need to identify your specific pivot table. Remember
that if more than one pivot table is attached to a pivot cache, then all
tables will be refreshed when you refresh one.

It is a crazy world we live in.

Need code assistance to update pivot table from an ADO records
 

Sorry, but I am in over my head on this.

Have you tried to add the code "ActiveSheet.PivotTables("Test
PT").PivotCache.Refresh" to your code following "Set objPivotCache.Recordset
= objRS" but before closing the database?

Tom

Need code assistance to update pivot table from an ADO records
 

Have tried that and variations of setting cache = recordset, but all give
errors.
Thanks for your help


"TomPl" wrote:

Sorry, but I am in over my head on this.

Have you tried to add the code "ActiveSheet.PivotTables("Test
PT").PivotCache.Refresh" to your code following "Set objPivotCache.Recordset
= objRS" but before closing the database?

Tom