Need code assistance to update pivot table from an ADO recordset

[Bryan S]

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:=xlExternal)
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

 

[TomPl]

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.

 

[Bryan S]

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]

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

 

[Bryan S]

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