I've created some code to change the database used in a PivotCache. (Based
on snippets found in this group.) Based on the MsgBox statement at the end,
it appears the database is changing, however a refresh of the table shows
the old data. Where am I going wrong? I'm finding it difficult to locate
good examples of changing SQL database.

Sub ChangeDatabase()
'Declare our variables.
Dim ptc As PivotCache, oldDB As String, newDB As String
Set ptc = ActiveCell.PivotTable.PivotCache

MsgBox "Connection: " & ptc.Connection ' Confirms the current settings

'Request the name of the old server/file name.
oldDB = InputBox("Input the name of the old database or file path as
listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newDB = InputBox("Input the name of the new database or file path which
you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently
active.

ptc.Connection = Application.Substitute(ptc.Connection, oldDB, newDB)
ptc.Refresh

MsgBox "Connection: " & ptc.Connection ' Shows new connection
information

End Sub


Thanks - John