"Kelvin" wrote:
> I've inharited a spreadsheet that connects to an external source, our SQL
> 2005 server.
> I've altered the query to reflect our server name and saved it in Excel as a
> query with a new name.
> If I press F5 I can select the range, then I click on the Edit Query icon .
> First it says "This query cannot be edited by the Query Wizard", I press OK.
> Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM,
> Statement(s) could not be prepared", which makes sense since their server is
> different then ours.
> If I press OK there it takes me to basically a blank screen/grid.
> If I click the SQL button there I can see their old query.
> If I click the Open button my query is in the list (I deleted their from
> this Open Query dialog box) and can select mine.
> My query opens fine and has my data there.

> How do I get this spreadsheet to look at my data rather then the old one I
> deleted?
> The Help menu was trying to get me to go to Edit/Links on the Excel menu,
> but Links is grayed out...

> I'm stuck, can someone help me out here?

> I'm using Excel 2003 if it matters

> Thanks

> Kelvin

> Hi,

> It might be safer, easier, and more flexible if you record a macro to do
> the
> thing from scratch.  Then instead of modifying the query, you could modify
> code.  If you have the original query, which worked, turn on the VBA macro
> recorder and right click the query range in the spreadsheet and choose
> Edit
> Query.  Just click Next Next, Next, Finish and then turn the recorder off.

> The important part of the code will look like this:

> With Selection.QueryTable
>        .Connection = Array(Array( _
>        "ODBC;DSN=MS Access Database;DBQ=C:\Class\Company
> 9040.mdb;DefaultDir=C:\Class;DriverId=25;FIL=MS
> Access;MaxBufferSize=2048;PageTimeo" _
>        ), Array("ut=5;"))
>        .CommandText = Array( _
>        "SELECT TH_Employee.DeptNo, TH_Employee.EmplNo,
> TH_Employee.FirstName, TH_Employee.LastName, TH_Employee.PhoneNo,
> TH_Employee.Street, TH_Employee.City, TH_Employee.State,
> TH_Employee.ZipCode,
> TH_Employ" _
>        , _
>        "ee.`With%`, TH_Employee.PerfRate, TH_Employee.BirthDate,
> TH_Employee.Dependents, TH_Employee.Check, TH_Employee.ArchiveDate" &
> Chr(13)
> & "" & Chr(10) & "FROM `C:\Class\Company 9040`.TH_Employee TH_Employee" _
>        )
>        .Refresh BackgroundQuery:=False
>    End With

> Of course your code connects to an Oracle database not an Access one so
> there will be differences.  A step at a time try modifying the code and
> test
> after each change by running the macro.

> --
> Thanks,
> Shane Devenshire

> "Kelvin" wrote:

>> I've inharited a spreadsheet that connects to an external source, our SQL
>> 2005 server.
>> I've altered the query to reflect our server name and saved it in Excel
>> as a
>> query with a new name.
>> If I press F5 I can select the range, then I click on the Edit Query icon
>> .
>> First it says "This query cannot be edited by the Query Wizard", I press
>> OK.
>> Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM,
>> Statement(s) could not be prepared", which makes sense since their server
>> is
>> different then ours.
>> If I press OK there it takes me to basically a blank screen/grid.
>> If I click the SQL button there I can see their old query.
>> If I click the Open button my query is in the list (I deleted their from
>> this Open Query dialog box) and can select mine.
>> My query opens fine and has my data there.

>> How do I get this spreadsheet to look at my data rather then the old one
>> I
>> deleted?
>> The Help menu was trying to get me to go to Edit/Links on the Excel menu,
>> but Links is grayed out...

>> I'm stuck, can someone help me out here?

>> I'm using Excel 2003 if it matters

>> Thanks

>> Kelvin