The connection property of a query cannot be changed. You need to delete the
query and recreate the query.You can get the other properties of the query to
make up a new query. I would like to see the code you found for a pivot
table. It probably can be modified for a query table.

The Comand Text portion of the qury is the SQL method.

You may want to use an Access Application in excel to gewt the data rather
than use queries. Any ACCESS VBA instruction can be used in excel. See
access VBA help.

First open an access application

Set obj = createobject("Access.Application")
or
Set obj = Getobject(filename:=database.mdb")

You also have to add areference to your excel vba project
from menu

Tools - References - Microsoft Access 11.0 Object library (or latest version
on your PC)

You also may hae to ad aditional references depending on the method you
choose.


Another choice is to have your macro run in access and export the data to
excel.
"Cinco" wrote:

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.