Sub PivotTableDataViaADO()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim pvc As PivotCache
Dim pvt As PivotTable
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & CurrentProject.Path & "SalesDB.accdb;"
sSQL = "Select * From SalesData"
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = con
rs.Open sSQL
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pvc.Recordset = rs
Worksheets.Add Before:=Sheets(1)
Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, _
TableDestination:=Range("A1"))
With pvt
.NullString = "0"
.SmallGrid = False
.AddFields RowFields:="State", ColumnFields:="Product"
.PivotFields("NumberSold").Orientation = xlDataField
End With
End Sub