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.

"Olivier Rollet" <Olivier 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.

--

for the OP:

A few suggestions:
from vba's immediate window
activesheet.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" wrote in message
om...
"Olivier Rollet" <Olivier 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.

--

"Rob van Gelder" wrote ...

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

I find code easier to reuse than mouse clicks <g.

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.

You AFAIK you can't use Querytables in VBA code or use the GUI query
builder tools to an update a database. However, if you type DML
(UPDATE, INSERT, DELETE etc) or DDL (CREATE TABLE, etc) statements
into MS Query's SQL window then it will be executed as expected (e.g.
subject to permissions) and report back the number of rows affected.

Obviously MS Query isn't optimized for anything other than simple
SELECT queries, however I don't agree that Excel isn't supposed to be
for database updates. I see Excel as a place for users to analyse and
edit data; the less restrictions the better. But when done it is
useful to have a way of passing amendments back to the data source. I
agree that server side processing (e.g. VIEWs and PROCEDUREs) should
be used where possible.

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?

It's been a couple of jobs/years since I supported Oracles databases.
Funny, I had the opposite experience i.e. the database was happiest
when using ADO! My only advice could be: ensure the Oracle client is
installed and use Oracle's own ADO provider rather than the Microsoft
version.

Jamie.

--

Don't get me wrong. I know Excel can be used as a front-end to databases,
for data entry.
Having created many of these solutions, I'm starting to realise the
difficulties in keeping these applications running peacefully.
My angle is that Excel gives you a lot of options - if you're not fully
aware of the effect each of these options will have on your workbook,
problems can occur.

Here's an example (which, fortunately has not happened to me)
Say you've queried a block of data to for manipulation.
There is a region which is surrounded by null values.
The user sorts that isolated region.
The user does some more stuff, unaware of the consequences their sort just
had.
The user then Saves back to the database.
Instant database corruption.

This particular issue could be addressed by using Lists in 2003, but there
are others gotchas too.

Recently, I've been building my apps differently.
I've been treating Excel as a smart-grid - locked down and protected.
Insert, Update and Delete buttons are available on the toolbar.
When a user double-clicks a cell (or hits the Update button), the entire row
is pulled into a userform. All data entry is done there. The userform
handles database updates when OK is clicked. The row (or entire sheet
depending) is updated on return.

This different approach is not all that different from standalone
application development - so I'm starting to wonder what I'm still doing in
Excel.

Standalone ActiveX controls (or even .NET these days) provide some pretty
good grids. Heirarchy type grids are starting to look really good and I'm
beginning to realise what the users are missing.


I've read some of your posts and know you're an expert in this area. I
wonder what approach you've taken with Excel - Database application
development?

Cheers


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jamie Collins" wrote in message
om...
"Rob van Gelder" wrote ...

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

I find code easier to reuse than mouse clicks <g.

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.

You AFAIK you can't use Querytables in VBA code or use the GUI query
builder tools to an update a database. However, if you type DML
(UPDATE, INSERT, DELETE etc) or DDL (CREATE TABLE, etc) statements
into MS Query's SQL window then it will be executed as expected (e.g.
subject to permissions) and report back the number of rows affected.

Obviously MS Query isn't optimized for anything other than simple
SELECT queries, however I don't agree that Excel isn't supposed to be
for database updates. I see Excel as a place for users to analyse and
edit data; the less restrictions the better. But when done it is
useful to have a way of passing amendments back to the data source. I
agree that server side processing (e.g. VIEWs and PROCEDUREs) should
be used where possible.

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?

It's been a couple of jobs/years since I supported Oracles databases.
Funny, I had the opposite experience i.e. the database was happiest
when using ADO! My only advice could be: ensure the Oracle client is
installed and use Oracle's own ADO provider rather than the Microsoft
version.

Jamie.

--

Rob,
Your analysis is good. I'll share some thoughts.

When I need to update a database from Excel I sometimes prefer the
'lazy' option and use a disconnected ADO recordset bound to an
updateable grid on a userform e.g. Microsoft DataGrid Control 6.0
(SP6) (OLEDB). Changes to data in the grid are subject to internal
restrictions (e.g. can't delete a column) and some simple validation
(e.g. for data type) and are propagated to the recordset. When ready,
reconnect the recordset and issue a batch update.

As you say, this doesn't really have the feel of a true Excel
application. Some say that an Excel has a perfectly good grid in the
Worksheet object <g. But considerable work is required to get a
worksheet to behave like the DataGrid. I find that so many things are
locked down in the process that, again, you end up with something
which isn't in the spirit of an Excel application.

Even when using the DataGrid, to be able to update the data source, a
key is required. Consider this table:

A B
------
1 2004-11-02
2 2001-01-01
3 2001-01-01

Each row can be identified because column A, our key, contains unique
values. Say this data is fetched to an Excel worksheet, one cell is
amended and sent back as:

A B
------
1 2004-11-02
4 2001-01-01
3 2004-11-02

Although only one cell was amended, two rows in the database are
affected: the row where A=2 has been deleted and a new row where A=4
has been inserted.

Say the data is fetched to Excel again, the user (accidentally?)
inserts a single cell and gets sent back as:

A B
------
1
4 2004-11-02
3 2001-01-01
2004-11-02

This time four rows are affected (although the new row where A is null
would presmably fail to be inserted).

Did this second round of updates make a mockery of data integrity?
Well the database should have constraints to take care of this aspect.
A client application should not be allowed to 'break' a database
simple by amending in tables for which it has write permissions.

The question is, did the change accurately reflect the user's
intention? The answer will depend on the circumstances. If it is it
not appropriate to allow users such free reign, a more restricted
approach will be required and perhaps Excel is not the best tool in
those circumstances.

Of course the above examples are just the tip of the iceberg e.g. an
amendment such as:

A B
------
1
2004-11-02
3 2001-01-01

creates all kinds of issues!

Personally, as a user I'd prefer to have a locked-down Excel app than,
say, a stand-alone app or a MS Access Forms-based application. Users
love Excel <g.

Jamie.

--

Thanks for the detailed reply.

I too have concerns about users accidently modifying the table structure
rahter than the table data.
My approach so far has been to lock down - but I've been unhappy with the
restrictions.
You put it nicely: "you end up with something which isn't in the spirit of
an Excel application"

I think my strategy will be to continue developing read-only reports in
Excel - read-write data entry in custom built standalone apps (or Excel
userforms)

As an ex-Access developer, I've missed the data entry ease of Datagrids with
autocomplete combos.

Again, thinks. It's good to know I'm not the only one cautious of Excel's
flexibility.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jamie Collins" wrote in message
om...
Rob,
Your analysis is good. I'll share some thoughts.

When I need to update a database from Excel I sometimes prefer the
'lazy' option and use a disconnected ADO recordset bound to an
updateable grid on a userform e.g. Microsoft DataGrid Control 6.0
(SP6) (OLEDB). Changes to data in the grid are subject to internal
restrictions (e.g. can't delete a column) and some simple validation
(e.g. for data type) and are propagated to the recordset. When ready,
reconnect the recordset and issue a batch update.

As you say, this doesn't really have the feel of a true Excel
application. Some say that an Excel has a perfectly good grid in the
Worksheet object <g. But considerable work is required to get a
worksheet to behave like the DataGrid. I find that so many things are
locked down in the process that, again, you end up with something
which isn't in the spirit of an Excel application.

Even when using the DataGrid, to be able to update the data source, a
key is required. Consider this table:

A B
------
1 2004-11-02
2 2001-01-01
3 2001-01-01

Each row can be identified because column A, our key, contains unique
values. Say this data is fetched to an Excel worksheet, one cell is
amended and sent back as:

A B
------
1 2004-11-02
4 2001-01-01
3 2004-11-02

Although only one cell was amended, two rows in the database are
affected: the row where A=2 has been deleted and a new row where A=4
has been inserted.

Say the data is fetched to Excel again, the user (accidentally?)
inserts a single cell and gets sent back as:

A B
------
1
4 2004-11-02
3 2001-01-01
2004-11-02

This time four rows are affected (although the new row where A is null
would presmably fail to be inserted).

Did this second round of updates make a mockery of data integrity?
Well the database should have constraints to take care of this aspect.
A client application should not be allowed to 'break' a database
simple by amending in tables for which it has write permissions.

The question is, did the change accurately reflect the user's
intention? The answer will depend on the circumstances. If it is it
not appropriate to allow users such free reign, a more restricted
approach will be required and perhaps Excel is not the best tool in
those circumstances.

Of course the above examples are just the tip of the iceberg e.g. an
amendment such as:

A B
------
1
2004-11-02
3 2001-01-01

creates all kinds of issues!

Personally, as a user I'd prefer to have a locked-down Excel app than,
say, a stand-alone app or a MS Access Forms-based application. Users
love Excel <g.

Jamie.

--