pivot table with external data

[dstiefe]

I am querying my database and saving it to a recordset...see below...know how
do i put that into a pivot? I only know how to write the code for an XL based
data source..

Thank you


Sub GetAccessData()
Dim sh As Worksheet

Set sh = Sheets("Sheet2")

Dim MyConnect As String

Dim MyRecordset As ADODB.Recordset

MyConnect = "Provider=Microsoft.ACE.OlEDB.12.0;" & _
"Data Source = C:\Dans_DB.accdb"

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open "DMA", MyConnect, adOpenStatic, adLockReadOnly

 

[SteAXA]

For create your pivot table you can do so:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "Provider=Microsoft.ACE.OlEDB.12.0;Data Source =
C:\Dans_DB.accdb"
.CommandType = xlCmdSql
.CommandText = your_query
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:=your_pivot_name, DefaultVersion:=xlPivotTableVersion10
End With

After for refresh your data:
ActiveSheet.PivotTables(sNamePivot).PivotCache.Refresh

Bye, Ste'