Peter

Instead of refreshing, invoke the pivot table wizard while in your pivot
table and press the 'back' button. Press Get Data... and it will likely
error, navigate to the file via the database dialog and it should then error
but ask if you want to continue in MSQuery. Answer yes and then navigate to
the file/table/query from there, select FileReturn data to MS Excel (In
MSQuery) and finish the pivot table dialog, save your file and all should be
well

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Peter" wrote in message
...
I have in Excel (2003) a Pivot table pointing at a Access database.

Now I have moved the Access database to a new location and want to point
the
Pivot table to that path. When I refresh my Pivot table I am warned that
the
original file does no longer exist and when I Click "OK" the Login
dialogue
appear where I can select a new database. If I select the new location of
the
database and finish this dialogue I still get an error message that Excel
can
not find the mdb-file in the original location.

I have studied other related topics on this site and it is suggested that
the following VBA bit is executed:

Sub ChangeConn()

Dim qt As QueryTable
Dim Wsh As Worksheet
Dim OldLoc As String, OldPath As String
Dim NewLoc As String, NewPath As String
Dim LastSlash As Long
Const Ext As String = ".mdb"

OldLoc = "C:\TempX\Finance follow-up"
NewLoc = "C:\Access\Production\Finance follow-up"

LastSlash = InStrRev(OldLoc, "\", , vbTextCompare)
OldPath = Left(OldLoc, LastSlash - 1)

LastSlash = InStrRev(NewLoc, "\", , vbTextCompare)
NewPath = Left(NewLoc, LastSlash - 1)

For Each Wsh In ThisWorkbook.Worksheets
For Each qt In Wsh.QueryTables
qt.Connection = Replace(qt.Connection, OldLoc & Ext, NewLoc
&
Ext)
qt.CommandText = Replace(qt.CommandText, OldLoc, NewLoc)
qt.Connection = Replace(qt.Connection, OldPath, NewPath)
qt.Refresh
Next qt
Next Wsh

End Sub

I have tried this as well - unfortunately the result is the same. What can
I
do to solve this problem - I would not like to have to re-design Pivot
tables
in my Excel spreadsheet.

I appreciate any help on this issue.

Best regards
Peter
(Copenhagen)