I have a spreadsheet that uses 20+ queries to access an SQL database. I want
to run the spreadsheet from multiple systems (backups, etc.) to which I can,
when needed, restore the database. Each backup system will have a new server
name as each has a unique WorkStation ID. How do I update the queries to
point to the new server? I tried updating the queries in the Microsoft Query
directory but they are not accessed by the spreadsheet which seems to have
its own imbedded copies. I did update and run successfully three queries
that were initially created in one of my macros - I just changed the WSID in
the macro. I found a posting that showed some VBA code to change the server
in a Pivot Table. Is there something similar for a Query? If not, is there
some way to avoid having to respond with the new server name when the
spreadsheet runs on a backup system? I have considered creating a macro with
all the queries and running that on the backup. Would that accomplish my
purpose of initializing the queries to point to the new server? Thanks for
your guidance on addressing this request. I would like to do this in the
simplest and fastest way.