MSQuery & ODBC Connection

aitai

New Member
Joined
Sep 10, 2002
Messages
3
Hi, All

Our IP addresses recently change, and for some reason, the msquery that pulls data from our SQLServer (7.0) into spreadsheets does not work (error message: connection failure). And we have, quite literally, hundreds of these making manual intervention impractical.

The IP address for the ODBC connection used has been updated (pointing to the right server). The problem is, for some reason, MSQuery is still trying to pull from the old ODBC settings.

I would appreciate any insight on this problem. Thank you.

Ivan
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Did you change the ODBC via MSQuery or through Control Panel - the latter I believe is the more robust. Other than that - could you change the name of the ODBC to something new and use that?
 

aitai

New Member
Joined
Sep 10, 2002
Messages
3
Thanks for your response.

The settings were changed in the ODBC control pannel (System DSN).

Creating a new System DSN would defeat the object of this post, as it would mean that we will have to update, manually, hundreds of MSQueries which is what we are trying to avoid.
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
The IP address is held in two places in the registry both should be changed using the ODBC control panel but can be done manually.

In the ODBC panel you may need to change the name TWICE. Once in the server name dialog box and again in the "Client Congiguration" box (found on the second screen of the config change to the ODBC connection - it's a button marked as "Client Configuration"

Hope this helps
 

aitai

New Member
Joined
Sep 10, 2002
Messages
3
Thanks for the suggestion. We have made the change in both places, with no luck.

The only option is to change the settings in the MSQueries. Problem now is, without the ODBC connection we can not get into the queries inn the first place. Thanks again.
 

Forum statistics

Threads
1,143,615
Messages
5,719,728
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top