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

--