Refreshing Excel Pivot based on Access DB when DB has moved

[John P]

I am developing an Access DB that will be utilized as the source for many different reporting pivot tables. In my testing if I create the pivots, move the Access DB to another folder and click refresh the application cannot find the DB and the refresh fails. My concern is that I am creating the DB for users who may place it within a different folder. Is there an area that I can change the code in the Excel pivot so it points anywhere I elect for the DB to exist.

I already have the DB completed as well as the pivots and tables that source the pivot table, so I am hoping there is a better way than recreating the DB and pivots on the users devices. Any suggestions are welcomed. Thanks

 

[Dugantrain]

MS Query, although handy and easy to learn, is a fairly weak tool. The only work-around that I've found for moved data sources is to rename the database in the Pivot's query's SQL (which isn't really a work-around at all based on what you're wanting to do). My only suggestion is to start getting into the world of Automation and have your databases use the Excel Object Model to build Spreadsheets on the fly. Takes a lot of knowledge and coding, but you'll find Automation to be infinitely more powerful than MS Query.