Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlExternal)
DBFile = ThisWorkbook.Path & "\budget.mdb"
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
QueryString = "SELECT * FROM '" & ThisWorkbook.Path & "\BUDGET'.Budget Budget"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="BudgetPivot")
With PT
.PivotFields("DEPARTMENT").Orientation = xlRowField
.PivotFields("MONTH").Orientation = xlColumnField
.PivotFields("DIVISION").Orientation = xlPageField
.PivotFields("ACTUAL").Orientation = xlDataField
End With
End Sub