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.

--