I've inharited a spreadsheet that I need to change the query to an external source...

[Kelvin]

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

 

[MX9]

Rebuild Query in New Worksheet

One good way to handle it, that I've had to use more than once, is to start with a new workbook, rebuild your external database query from scratch. The reason is, the existing spreadsheet has a query range embedded in it and when you go to connect or refresh or edit, the worksheet is defaulting to the external query that was originally used to extract the data and not the additional query you have written.

 

[ShaneDevenshire]

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.

 

[Kelvin]

Thanks for the advice, I'll give that a try

Kelvin