Changing ODBC data source for a query embedded in Excel

[Matt Colegrove]

I have data in an SQL Server and an Excel (2003 sp2) worksheet that uses an
embedded Database Query to retrieve the data. All worked fine until the SQL
Server hosting the data was migrated to a new box.

How does one update the embedded data source to point at the new server?
Every time I open the query, it errors because it cannot connect to the old
server. I have updated the ODBC configuration to point to the new server,
but Excel appears to have retained the original server connection information
and doesn't want to give it up.

Is there any way to change the data source without having to rewrite all the
embedded queries?

The Excel help files show how to create new datasources, but have nothing on
how to updating an existing datasource.

 

[CLR]

If the Query is run off an Excel macro, then the macro must be edited to
make the change. If you do not have the resources to edit the macro, then a
new one must be recorded using the macro recorder......which incidently
might not work right itself the first time and may need editing itself.
This is one of those things thats kinda/sorta the job for a pro. If you
have a MIS dept, I would turn it in as a trouble report and have them to fix
it since they are the ones who caused it to go bad in the first place.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Matt Colegrove]

Thanks for the response. The macros aren't the problem. The data source
info seems to be embedded in the worksheet and there doesn't seem to be a way
to change it without deleting it and recreating a new query. I need to be
able to change the data source info associated with the query.

 

[Matt Colegrove]

I just found a workaround for this.

Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the following in front of the string:

ActiveCell.QueryTable.Connection =

Move the cursor to the end of the string and press enter. This will store
the updated connection string back into the worksheet. Try your query now.

Repeat for any other embedded queries in your worksheet.

 

[CLR]

Cool..........thanks for closing the loop

Vaya con Dios,
Chuck, CABGx3