I tried to record the code to update a pivot sourcedata which gave me this:

ActiveSheet.PivotTableWizard SourceType:=xlExternal, _
    SourceData:=QueryArry1, _
    Connection:=Array( _
        Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _
        Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
    )

But this doesn't even allow me to specify WHICH pivot table I want to update... or even do what I really want to do which is update the pivotcache so that all pivot tables using that same source are updated.

So what is a good way to update the sourcedata?

Thanks

I tried to record the code to update a pivot sourcedata which gave me this:

ActiveSheet.PivotTableWizard SourceType:=xlExternal, _
    SourceData:=QueryArry1, _
    Connection:=Array( _
        Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _
        Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
    )

But this doesn't even allow me to specify WHICH pivot table I want to update... or even do what I really want to do which is update the pivotcache so that all pivot tables using that same source are updated.

So what is a good way to update the sourcedata?

Thanks

EDIT:

But I even get the "application-defined or object-defined error" error with something as simple as:

str = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText
Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = str

And I did double check that my pivot table is still hitting the live data and refreshing it still works... yet I can't set the command string to what it is currently? So bizarre.

Thanks