i have created an ADO SQL query and built 2 pivots from the resulting pivot cache. Problem is where i would like the user to tweek the query (lets say a different date range to use) and the new resultset to refresh the pivots without having to rebuild the pivot tables.

i have tried to achieve this by calling the original funtion in 2 different modes:
mode 1: to query and build tables from scratch
mode 2: to requery but only refresh the tables (no rebuild)

call Create_PivotTable_ODBC_MO("rebuild")
first time round the sql runs and pivots get built OK.

we tweek the date range on the sheet (see SQL ) and then:

call Create_PivotTable_ODBC_MO("changeQuery")

the routine runs through making a new ADO executing the new SQL statement, and finally
calls UpDate_Pivottable_MO()

but does not change the data in the pivot tables.

i have read about ptCache.EnableRefresh = True but this has not worked.

not sure if this is important but the database is Access 97 and i use excel 2003.

any pointers appreciated.

below i have included the main function with sql and pivot build code with mode switches and a helper refresh function.

'====================================================

Public Sub Create_PivotTable_ODBC_MO(strMode As String)

Dim stCon As String
Dim stSQL As String
Dim rngTemp As Range

stCon = "ODBC;DSN=MS Access Database;DBQ=\\XXXXX.mdb;uid=Admin;pwd=;MaxBufferSize=2048;PageTimeout=5"

stSQL = "SELECT * FROM YTDData where TradeDate > " & "#" & CDate(Range("startDate").Value2) & "#" & " AND TradeDate <= " & "#" & CDate(Range("endDate").Value2) & "#"

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("main")

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = True
End With


'Create the pivotcache.
Set ptCache = wbBook.PivotCaches.Add(xlExternal)
ptCache.EnableRefresh = True

'Populate the pivotcache.
With ptCache
'.OptimizeCache = True
.Connection = stCon
.CommandText = stSQL
.CommandType = xlCmdSql
End With

If strMode = "rebuild" Then

'Create and add a pivottable.
Set ptTable = wsSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=wsSheet.Range("B10"), TableName:="ADO_Table")

'Manipulate some of the pivottable's properties.
With ptTable
.AddFields Array("TradeDate", "txt_Unit"), Array("Product_Category"), "SalesPerson" 'rows then cols then page totals
.PivotFields("SalesPerson").CurrentPage = "All"
.PivotFields("AVTotal").Orientation = xlDataField
.PivotFields("Count of AVTotal").Function = xlSum
.format formatStyle 'this is the style of the table

.PivotSelect "TradeDate", xlLabelOnly
Selection.Group Start:=True, End:=True, End:=73076, Periods:=Array(False, False, False, False, True, False, False) 'end date = 73076

Set rngTemp = .DataBodyRange
Call formatPivot(rngTemp)

Set rngTemp = Nothing
End With


'Create and add another pivottable using the SAME CACHE.
Set ptTable2 = wsSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=wsSheet.Range("J10"), TableName:="ADO_Table2")
With ptTable2
.AddFields Array("TradeDate", "txt_Unit"), Array("Product_Category"), "SalesPerson" 'rows then cols then page totals
.PivotFields("SalesPerson").CurrentPage = "All"
.PivotFields("Product").Orientation = xlDataField
.PivotFields("Count of Product").Function = xlCount
.format formatStyle 'this is the style of the table

.PivotSelect "TradeDate", xlLabelOnly
Selection.Group Start:=True, End:=True, End:=73076, Periods:=Array(False, False, False, False, True, False, False) 'end date = 73076

Set rngTemp = .DataBodyRange
rngTemp.Select
Call formatPivot(rngTemp)

Set rngTemp = Nothing

End With

'hide the field window
ActiveWorkbook.ShowPivotTableFieldList = False

End If

If strMode = "changeQuery" Then

Call UpDate_Pivottable_MO

End If


'Restore the settings.
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

'===================================================

Sub UpDate_Pivottable_MO()

Dim pc As PivotCache
Dim pt As PivotTable
Dim sh As Worksheet

Application.EnableEvents = False

For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc

For Each sh In ThisWorkbook.Worksheets
For Each pt In sh.PivotTables
pt.RefreshTable
Next pt
Next sh

Application.EnableEvents = True


End Sub