MS Query and Excel problem

[Enya]

Hi, I have an Excel workbook that has several MS Queries that retrieve data from a Microsoft SQL server, via ODBC connection. We recently changed the server that my database resides on, so I updated my ODBC connection with the new server name. Fine, no problems there. Now when I try to refresh my queries, I get an error saying "Server rejected connection, access to database denied". So then I manually went into each .DQY file and changed the server paramater. Still the same error. The crazy part is, I can run these queries outside of my workbook. I even tried deleting the query from the workbook, and setting it back up again by doing data/get external data/run saved query. Same error. There's something behind the scenes that is still pointing to the old connection. It's making me nuts!! Has anyone run into this problem? Am I missing something obvious?
Thanks for any help!

 

[misscrf]

It sounds like your having a security issue, connecting to the data source. Usually, you get a sql server login prompt. Do you get that? are you using a trusted connection? Did you set up the ODBC connection in your administrative tools odbc services? I have to ask the obvious questions, but I promise to help you get somewhere.

misscrf

Management is doing things right, leadership is doing the right things

 

[Enya]

No, it's not a security issue. I can use that ODBC connection in other workbooks. I don't get a sql server prompt because we use Win NT authentication to connect to our servers. Yes, the ODBC connection is set up in the admin tools/data sources.

 

[misscrf]

Can you try copying the data from the sheets into a new workbook and then just reconnecting them to their saved queries?

This won't find out the issue, but it might solve the problem. It looks like there is some sort of hidden old connection that has not been found.

misscrf

Management is doing things right, leadership is doing the right things