Change Server for Query

[Cinco]

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.

 

[Joel]

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]

Joel,

Thanks for the explanation of how query connection works. Here is the code
that I found for a Pivot Table. If you can change it to work with a Query
Table, I would be thankful.
Jim

Subject: Re: External Data Query 9/4/2006 6:36 AM PST

By: Carim In: microsoft.public.excel.misc


Marie,

You are right ... View Code and then the icon Object Browser ...

Meanwhile, found the following VBA code to ChangeServer

Sub ChangeServer()
'Declare your variables.
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = InputBox("Input the name of the old server or file path as

listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newSrv = InputBox("Input the name of the new server or file path
which
you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently
active.
Set ptc = ActiveCell.PivotTable.PivotCache
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv,
newSrv)
ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub


HTH
Cheers
Carim

 

[Cinco]

Joel,

A few days ago, I found the answer to my question in a posting by K. Dales
dated 2/14/2005. He wrote:

The Query Table Connection property contains the connection string. I have
never tried changing the connection this way but it should be possible. I
would go to the immediate pane and try this:
?Sheets("SheetName").QueryTables(1).Connection
to see what the current string looks like.

Then use:
Sheets("SheetName").QueryTables(1).Connection = "modified current string"
to reset the connection parameters.

I am now using this approach in a Macro to change my 20 queries when I move
my spreadsheet from one PC/Server to another. It takes about a second and
works Great!

Jim