MSQuery & ODBC Connection

[aitai]

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

 

[DonkeyOte]

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]

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]

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]

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.