Changing ODBC data source for a query embedded in Excel
 

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.

Changing ODBC data source for a query embedded in Excel
 

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" wrote in message
...
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.

Changing ODBC data source for a query embedded in Excel
 

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.

"CLR" wrote:

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" wrote in message
...
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.

Changing ODBC data source for a query embedded in Excel
 

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.



"Matt Colegrove" wrote:

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.

"CLR" wrote:

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" wrote in message
...
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.

Changing ODBC data source for a query embedded in Excel
 

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

Vaya con Dios,
Chuck, CABGx3



"Matt Colegrove" wrote in message
...
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.



"Matt Colegrove" wrote:

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.

"CLR" wrote:

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" wrote in
message
...
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.