adds a PivotTable based on the data from an Access database

     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