Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Database links?
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 (DataEdit Links) Any advice would be very much appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Database links?
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" wrote: 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 (DataEdit Links) Any advice would be very much appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Database links?
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? -- Rosalyn Bentley Financial Controller The Mileage Company "joel" wrote: 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" wrote: 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 (DataEdit Links) Any advice would be very much appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing Database links?
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 -- Regards Roger Govier "RozBentley" wrote in message ... 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 (DataEdit Links) Any advice would be very much appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
Changing the source of links. | Excel Discussion (Misc queries) | |||
Database-like cross-worksheets links | Excel Discussion (Misc queries) | |||
Links to External Database | Excel Discussion (Misc queries) | |||
Changing Links | Excel Discussion (Misc queries) |