For create your pivot table you can do so:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.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.Ref resh

Bye, Ste'