|
|
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:
[Quoted Text] > 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
>
>
>
|
|
Thanks for the advice, I'll give that a try
Kelvin
"ShaneDevenshire" <ShaneDevenshire[ at ]discussions.microsoft.com> wrote in
message news:AAA60BE6-AE4D-4361-B728-FB8ED1A5DEE6[ at ]microsoft.com...
[Quoted Text] > 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
>>
>>
>>
|
|
|