Hi,

I'm trying to write some VBA code to ask the user the new location of an
Access database that is used as the source for an Excel PivotTable, then
points that PivotTable to the new connection.

Pseudo code is this:

With ActiveWorkbook.PivotCaches(1)
.Connection = strDBConn
.CommandText = strDBCTxt
End With

Where strDBConn is the connection string and strDBCTxt is the SQL for the
query. The first part (.Connection) works fine, but Excel halts with a "1004
error" on the assignment of CommandText.

I have checked the contents of the string - and done two tests: first,
re-write the code to create a new PivotCache which works with strDBConn and
strDBCTxt; then a test of the original method where it assigns the current
contents to a temporary string, then re-assigns it back, but it still falls
over with the same error.

What am I doing wrong?

Thanks,
Mark