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.

--