Cannot edit query link to sql sever

Hi

What kind of links do you have?  Are these query tables, pivot tables or what?  Assuming QTs, you should be able to go to the VBE and open up the Immediate Window (Ctrl+G) and type in the following:

?Activesheet.QueryTables(1).Connection

and see the actual connection string returned (do this when the data sheet is visible in your Excel window).  Note this is read/write so you should be able to modify it in the same way.

Richard

Richard:

Thanks for your response.

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.

?Activesheet.QueryTables(1).Connection
ODBC;DSN=GFMS;Description=SQL Server DSN for GFMS;UID=himshah;APP=Microsoft Office 2003;WSID=FIN-HIMSHAH;DATABASE=GFMS2007;Network=DBNMPNTW;Trusted_Connection=Yes;UseProcForPrepare=0
qurey-error.png

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.

If you create a new query table from within Excel by (on a fresh new sheet) going

Data>Import External Data>New Database Query

is the DSN listed in the Database tab? If not, click on Options and then Browse and locate where your DSN is saved down.  Then click OK.  You should then see it listed under Databases .  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.  Try adding one and a few columns from it, and see if records are returned.  This will prove that your DSN *can* work in Excel.  Knowing this, we can then look to why it currently isn't.

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).Connection.  We can then see if there are any differences between the connection string in this (working) query and in your others.

Richard

Error message I posted above was when I try to edit the queries I created before the serve was moved.

Data>Import External Data>New Database Query

is the DSN listed in the Database tab?  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.

This is the response with fresh new link - VBE

Data>Import External Data>New Database Query

Activesheet.QueryTables(1).Connection
ODBC;DRIVER=SQL Server;SERVER=HARDINGSQL\GFMS_HARDING;UID=sa;;APP=Microsoft Office 2003;WSID=HARDINGTS1;DATABASE=GFMS2008

It is different then what is responded with old links but I don't know how to edit the old query to fix it.

Hi Richard:
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?

Again, thanks for your help.