Mixing OpenOffice/LibreOffice, MySQL, MacOS/Windows, and ODBC/JDBC

This post is about some difficulty I had in making an Apache OpenOffice Base “odb” front end work with an MySQL database backend on Mac OS X, and how I got things working.

I originally had an Apache OpenOffice (AOO) Base “odb” file working on Windows, accessing MySQL with ODBC.  The “odb” file had a few forms, which makes data entry much easier than what I could do with MySQL WorkBench, while still much easier to setup than making a web front end for something that I’m doing the data entry myself (not worth effort).

That was fine, but then I got a MacBook Pro, and I couldn’t use OpenOffice Base on the Mac.  I found there’s no ODBC facility built into the latest versions of Mac.  When I found an ODBC manager from Apple, I could download, the interface was terrible, and it essentially didn’t work.

So, I thought I’d try JDBC, instead of ODBC.  That was a pain.  AOO never seemed to find the Java VM in the Mac.  And, it couldn’t seem to find the driver, giving a rather cryptic message.

Ultimately, I realized the solution was to ditch AOO, and go with LibreOffice (which is a forked from the same source code, and fully compatible).  LibreOffice seems to have wider usage, and it’s easier to find help with.  It also seemed to magically find the the Java VM that AOO could not.  At first, it couldn’t load the JDBC driver.  All I had to do to fix this, was Download Connector/J from MySQL.com.  I selected the ZIP file option, unzipped it.  Next, I put the file ending in “.jar” in the Mac folder called /Library/Java/Extensions.  Now, I just created a Base “odb” file, choosing to connect to an existing database, selecting MySQL, and JDBC.

I then, went to my old “odb” file, “copied” the forms I had, and then “pasted” each into the new “odb” file in LibreOffice.  Now, everything worked on my Mac, but this new “odb” file didn’t yet work on Windows.  So, I needed to get JDBC to work on Windows.  For that, I went into LibreOffice, selected Tools/Options/Advanced, clicked on “Class Path…”.  then “Add Archive” and selected the “.jar” file (from Connector/J).  I assume I could have just done this on the Mac as well, and it would have been simpler.

Now, the same LibreOffice Base “odb” file works exactly the same on Windows as it does on the Mac.