Re: Stop to modify the SQL query manually entered into query !

From: Rob van Gelder (newsgroups_at_nojunkmail-vangelder.co.nz)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 23:55:34 +1300

for the OP:

A few suggestions:
from vba's immediate window
active***.querytables(1).commandtext = "select productname pname from
myproducts"
or
Dump your query 1 row down without column headers and manually type your own
into the first row.
or better still
Create a view

After years of using Oracle -> ADO -> VB -> Excel, I'm loving GUI and wizard
shortcuts.

I've lost count the number of times I've written.
Do Until rst.EOF
    ...
    rst.MoveNext
Loop

What I'm really starting to appreciate is the "Get External Data" feature of
Excel.
Sure, it doesn't do database updates, but that's not what Excel is for.
Yes, MSQuery is an obsticle, but I'm going to write an add-in to replace the
Edit button so you just dump the SQL in and be done with it.
It handles adding and subtracting rows nicely and even fills in adjacent
formulas.

You can go the ODBC route, or the ADO route. ADO seems slicker and it's not
ancient like odbc so it's got that coolness factor.
When using against Oracle drivers I use ODBC. Not by choice, but because for
some reason I can't get parameters working properly. ODBC works fine, ADO
doesn't. Any clues? 

-- 
Rob van Gelder - http://www.vangelder.co.nz/excel
"Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
news:2ed66b75.0410290149.5f85e3b5@posting.google.com...
> "Olivier Rollet" <Olivier Rollet@discussions.microsoft.com> wrote ...
>
>> with Oracle,  when using functions in a manual SQL query into Excel, the
>> alias for the ciolumns are SYTEMATICALLY  ignored or dropped .
>> There is no workaround by trying to edit it via the GUI because the
>> genrarated SQL doesn't work ! So,  it needs to write a macro to correct 
>> this.
>> Easy, but boring .
>> However, you should really think about  do someting of this obsolete 
>> piece
>> of junk named MsQuery.
>
> Try using the more 'modern' ADO in VBA code - no GUI tools or wizards
> to mess with your SQL code! Better still, write a server side stored
> procedure and simply pass the required parameters from Excel - no SQL
> on the client side!
>
> Jamie.
>
> --