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.

--