Child pivot table's source breaking from parent pivot table

[Michael Kucan]

I have a workbook with several pivot tables. There is one parent pivot table
that is the source for all child pivot tables. When I manually refresh the
parent pivot table, all child pivot tables are automatically refreshed. This
works great.

The parent pivot table's source is an ODBC connection to our warehouse. I
have to modify the WHERE clause of the SQL statement daily to update the
parent pivot table.

When I use code to alter the SQL of the parent table, all of the child
tables seem to break their source link from the parent, and continue to use
the old ODBC connection and SQL statement.

When I use code to try to repoint the child tables back to the parent, I get
an "Application-defined or object-defined error" error. Here is the snippet
I use to update the parent table (arySQL contains the SQL statement as a
one-dimensional array):

xlWS_Daily.PivotTables("DailyPivotTable").PivotTableWizard _
SourceType:=xlExternal _
,Connection:="ODBC;DSN=;UID=;;DATABASE=;ACCOUNT=;" _
,SourceData:=arySQL

I use the sparse Connection property, so that the client is prompted for the
ODBC connection every time.

Here is how I'm trying to re-point one of the child tables. This is where I
am getting the error:

xlWS_Monthly.PivotTables("MonthlyPivotTable").PivotTableWizard _
SourceType:=xlPivotTable _
,SourceData:="DailyPivotTable"

I recorded a macro of me manually re-linking the tables, and the above
statement was what it grabbed. However, when I'm running the script, it
throws the error.

I know I'm doingn something wrong, but I can't figure it out. Any ideas?

Thanks in advance for any help I can get.