Editing external data via ODBC

[Lester]

Hi,

I'm in the process of transitioning an excel/access (2003) based
solution to a web-based one. Right now, we have a dozen or so separate
spreadsheets and a couple of access databases that get updated several
times a day. I've managed to import most of the data into a mysql db
which I've tried to normalize as much as possible. This will be the
backend db for the webapp.

I've set up an ODBC connection from excel to the db and it works like a
charm; a few query tweaks and I can reproduce the data from all the
separate spreadsheets. But now, how about going the other way? I.e.,
can you set up an ODBC connection to *change* the data in a database?
I doesn't appear so based on what I'm seeing in excel. Changing data
in the spreadsheet does not show up when I click refresh on the
external data toolbar.

Is this a feature, or is there a way to override this behavior? I'd
really like to have db read/write functionality via odbc so the users
who are used to the excel interface can keep using it. It'd be neat to
have that kind of transparency, where everyone is working with the same
data whether its via a thick or a thin client.

Any tips would be appreciated!

Thanks

 

[Conrad Carlberg]

Hi Lester,

can you set up an ODBC connection to *change* the data in a database?

AFAIK (and I know a fair amount about this), you're going to need to involve
VBA to effect what you're looking for. But it's certainly possible.

Using mysql, I think you're going to have to establish an ADO connection in
VBA. How you go about it depends on how you want to change the data in the
db. Generally, the more efficient way (in terms of resources) is to use VBA
to execute SQL queries and sprocs, and VBA/ADO supports this. If you need to
do case-by-case processing, it might be better to use recordsets, at the
cost of less efficient use of resources. Recordset processing is also
supported by VBA/ADO, and can make it more straightforward to find, edit,
add and delete records using criteria.

Although it doesn't discuss mysql directly (the sections on ADO tend to
focus on SQL Server), my book Managing Data With Excel (Que, 2004) does go
into some depth on using ADO in Excel VBA modules to modify databases. If
you were still using Access, I'd suggest DAO instead of ADO, although either
would work.

FWIW, I think you're on the right page as to keeping the users on the Excel
interface. For all their power, relational databases are much less flexible
than the Excel worksheet/workbook, and you're going to go nuts trying to get
a database to return a LINEST, PMT or MINVERSE result.