Can MS Excel 2003 be used to //edit// a MySQL database?

Question

Does anyone know of a way to make Excel 2003 push changes to a MySQL database?

Currently, we're using Excel 2003 for keeping track of a big bunch of interconnected equipment (actually, an entire electric power distribution system with all the hoo-ha's that hang off it.) Quite frankly, using a spreadsheet for this sucks and I'm sick of it, so I want to convert it over to a Real Database like MySQL.

The gotchas are:

• The people who are using it aren't overly computer savvy. Using Excel to edit the MySQL database saves them the pain of learning something new.
• We don't have a license for MS Access, or I'd use that instead.
• I don't have the time to roll my own custom app.

I've already grabbed the MySQL ODBC driver and installed it, and importing data from MySQL to Excel is a breeze; but there's no obvious way to to the reverse, and push changes in Excel back to MySQL.

Answer 1

@Roland Bourman's comment (which would deserve to be an answer in itself) is a very interesting approach. If you're not a VB expert, you could mabye even do the pull/push outside of Excel on script level, pulling the data from mySQL into an excel sheet before opening Excel (easy) and writing it back after closing it. This could be done using a variety of tools.

Answer 2

There are (at least) two possible approaches here.

Use a MySQL database and, as you suggested, use the ODBC driver to update the database. Using ActiveX Data Objects (ADO) can let you accomplish this. See here for details

Alternatively, you can create a Microsoft JET database and use that instead of MySQL. This can be done using ADOX to create the database - see here - and then ADO to update it.

Both ADO and ADOX are available on my standard Office 2003 install (which does not include Access) so hopefully your system will have them too.

As a fallback if these aren't available, you could look at Data Access Objects (DAO) - the predecessor to ADO - but ADO seems a lot easier to use to me. DAO details are here