[John Michl[_2_]]

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

[John Michl[_2_]]

Sorry about the typos in previous message. It appears my spell checker was
"improving" my English. - John

"John Michl" wrote in message
...
I believed I figured out the problem but have not solved it. Though I was
change the source of the data, I was not change the SQL statement which
still referred to the original database. When trying to change that
statement, I learned that I can't since when I set up this workbook, I
copied one pivot table and pasted as others then changed some of the
fields.
Does anyone know how to change the SELECT statement in the SQL query under
these circumstances? Thanks - John

"John Michl" wrote in message
...
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