<div>
Hi<br />
<br />
What kind of links do you have? &nbsp;Are these query tables, pivot tables or what? &nbsp;Assuming QTs, you should be able to go to the VBE and open up the Immediate Window (Ctrl+G) and type in the following:<br />
<br />
?Activesheet.QueryTables(1<wbr />).Connecti<wbr />on<br />
<br />
and see the actual connection string returned (do this when the data sheet is visible in your Excel window). &nbsp;Note this is read/write so you should be able to modify it in the same way.<br />
<br />
Richard<br />

</div>


<div>
Richard:<br />
<br />
Thanks for your response.<br />
<br />
It is a query tables. Brings SQL Table data into excel. I don't use VBE. It is straight excel. I setup the New Database Query in Excel. PLease let me know how I can find it in excel.
</div>


<div>
?Activesheet.QueryTables(1<wbr />).Connecti<wbr />on<br />
ODBC;DSN=GFMS;Description=<wbr />SQL Server DSN for GFMS;UID=himshah;APP=Micro<wbr />soft Office 2003;WSID=FIN-HIMSHAH;DATA<wbr />BASE=GFMS2<wbr />007;Networ<wbr />k=DBNMPNTW<wbr />;Trusted_C<wbr />onnection=<wbr />Yes;UsePro<wbr />cForPrepar<wbr />e=0<br />
<a href="https://filedb.experts-exchange.com/incoming/2009/03_w12/117416/qurey-error.png" target="_blank" class="file-inline" title="error message (6 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>qurey-error.png</a>
</div>


qurey-error.png

<div>
The ODBC connection GFMS is connecting fine when you go to admin tool/data source(odbc). Why excel is not connecting is the issue. Also, the VBE code did not revel what SQL view I am using to extract data.
</div>


<div>
If you create a new query table from within Excel by (on a fresh new sheet) going<br />
<br />
Data&gt;Import External Data&gt;New Database Query<br />
<br />
is the DSN listed in the Database tab? If not, click on Options and then Browse and locate where your DSN is saved down. &nbsp;Then click OK. &nbsp;You should then see it listed under Databases . &nbsp;Click on it and hit OK - this should take you to the query wizard assuming everything is working OK and you should see the db tables listed there. &nbsp;Try adding one and a few columns from it, and see if records are returned. &nbsp;This will prove that your DSN *can* work in Excel. &nbsp;Knowing this, we can then look to why it currently isn't.<br />
<br />
Incidentally, if this does work, please return the records to Excel, then repeat the process I suggested above - open up the VBE, open up the Immediate window, and type in the ?Activesheet.QueryTables(1<wbr />).Connecti<wbr />on. &nbsp;We can then see if there are any differences between the connection string in this (working) query and in your others.<br />
<br />
Richard<br />

</div>


<div>
Error message I posted above was when I try to edit the queries I created before the serve was moved. <br />
<br />
Data&gt;Import External Data&gt;New Database Query<br />
<br />
is the DSN listed in the Database tab? &nbsp;Yes. The old dns GFMS (in cap) and new gfms(lowercase). The old GFMS(in cap) will error out as it seems to be looking for the old sql server. Therefore the queires created fails. I cannot see what view on sql server I used in creating my link.<br />
<br />
This is the response with fresh new link - VBE<br />
<br />
Data&gt;Import External Data&gt;New Database Query<br />
<br />
Activesheet.QueryTables(1)<wbr />.Connectio<wbr />n<br />
ODBC;DRIVER=SQL Server;SERVER=HARDINGSQL\G<wbr />FMS_HARDIN<wbr />G;UID=sa;;<wbr />APP=Micros<wbr />oft Office 2003;WSID=HARDINGTS1;DATAB<wbr />ASE=GFMS20<wbr />08<br />
<br />
It is different then what is responded with old links but I don't know how to edit the old query to fix it.
</div>


<div>
Hi Richard:<br />
Thanks for the resposne. It is working with the new command. The only issue I have it that I have multiple links in a single excel file. The proposed solution opens up only signle link. Is there a way to change it all link within a file?<br />
<br />
Again, thanks for your help.
</div>


<div>
<div class="content wysiwyg-content">
I had setup several complex link in excel 2003 to MS-SQL 2003. The link were setup using ODBC DSN.<br />
<br />
The company decided to upgrade and move SQL server to SQL 2008 with new SQL server name.<br />
<br />
I change ODBC DSN to link to the new server and my financial applicaiton works fine. The problem is with Excel. It gives error that SQL server dose not exists. It seems that Excel setup is looking for old server. It will not allow me to even Edit the query to change the link to new server. <br />
I have several years of files that needs to be updated. Is there a location where i can find the link outside of excel to oepn and change the link?<br />
<br />
<br />

</div>
</div>


I had setup several complex link in excel 2003 to MS-SQL 2003. The link were setup using ODBC DSN.

The company decided to upgrade and move SQL server to SQL 2008 with new SQL server name.

I change ODBC DSN to link to the new server and my financial applicaiton works fine. The problem is with Excel. It gives error that SQL server dose not exists. It seems that Excel setup is looking for old server. It will not allow me to even Edit the query to change the link to new server. 
I have several years of files that needs to be updated. Is there a location where i can find the link outside of excel to oepn and change the link?




Cannot edit query link to sql sever

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft applications include a variety of software programs, including development and digital authoring programs (Expression and Media Center), educational programs, Internet software, including Essentials, Skype and the Live family, anti-virus, productivity applications and suites like Office, Excel, Word, Outlook, Access and PowerPoint, video games and server applications such as Exchange, SharePoint, IIS and Virtual Server.

Microsoft Applications

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.