Changing Database links?

[RozBentley]

I have an access database (2007) containing data which I have summarised via
Pivot Tables in an Excel spreadsheet (2007) using Microsoft query (and pivot
the data in excel). The access database contains realtime information, and
hence the pivot tables are refreshed daily to review performance.

I need to move the file location of the database, but when I do this, the
pivot tables are no longer valid, and I can't update them.

I would like to know if there is a way you can move the database and then
update all links in the excel workbook without having to re-build all the
queries individually and link them to the new location of the moved access
database.

Is there a way to update links, similar to how you would in Excel (Data>Edit
Links)

Any advice would be very much appreciated

 

[joel]

You can't change the connection properties in queries but you can retrieve
the old SQL statements and paste then into a new query.

If you click on a cell in the Pivot Table then go to menu

Data - Import External Data - Edit Query

the edit window will have a SQL button. Copy the text from this window.
then create a query to a new database. go to Edit Query again and past ethe
text you extracted fro the old query.

 

[RozBentley]

Thank you for your reply Joel, yes, I know that I can copy SQL, but as I have
so many queries in so many spreadsheets, I was hoping their might be an other
way?

 

[Roger Govier]

Hi Roz

Take a look at the PivotPlay Addin, created by Ron Coderre.
You can find it on Debra Dalgleish's site
http://www.contextures.com/xlPivotPlayPLUS01.html