Better Base Databases

October 19, 2014 at 7:49 pm | Posted in Computers, Software | Leave a comment

The open source office suite OpenOffice and the related branch LibreOffice (my fav) are a great package for your word processing and spreadsheet needs – for free. I’ve been using them for years, ever since MS Office started locking up my machine – even when it wasn’t running. I’ve used OO for complex projects like a book with master document and separate chapter documents, auto-table of contents, end notes and more. They work fine with MS Office documents but do even better with the Open Document standard. This standard avoids some of the issues with proprietary formats. (ever tried to open a very old Word document?)

Where the package is weaker is with Base, it’s database component. Data is much more robust when stored externally from the reference document. I had a spreadsheet that was getting a little too large and decided to migrate it to a database in Base. I could then add more fields, have a data entry form, predesigned reports and so much more. I attempted to use an external SQL database (mySQL) but this required a bunch of added software and a very obscure process. While I successfully created a database, it wouldn’t link properly. I needed something more straightforward to manage.

I ended up just using Base, but with a default embedded database.  (Microsoft Access has the same issue btw) As is not so uncommon, it recently hiccuped and was unable to repair 2 components. Happily I use FileHamster, so was able to quickly recover a version saved prior to the hiccup.

But I knew I needed a better solution. This time, I ran into a more straightforward alternative for an external database. This uses JDBC and macros to manage your external data. The database is stored in the folder, outside the ODB file and is thus less prone to being corrupted. The folder is fully portable.

The instructions look long and complex but it’s actually pretty straightforward, with lots of detailed notes. Just follow the steps. I’ve summarized them below to give you a sense of it. They’re the same with OpenOffice or LibreOffice.

For a new database:
– check 2 correct settings (I’d recommend Medium security)
– download and put the Split_HSQLDB_Wiz template in a new folder
– open the template in Base and begin creating Tables and so forth
You’ll notice the 3-4 table files are created outside the ODB file, hence external.

Here are the steps in detail

For an existing database:
– check 2 correct settings (I’d recommend Medium security)
– create a new database folder
– copy your old ODB database into the folder
– using a Zip program like 7-Zip, extract the files in the database folder (your tables) of the old database into the new folder
– rename the extracted files to match the new database name, as in
– download and put the mydb_wiz.ODB and jar file in the folder
– open the new ODB and your tables will be seen “within”
– open the old ODB file and drag and drop the Queries, Forms and Reports into the new one
Save and done.

Here are the steps in detail

You can then delete the old ODB and rename the new one appropriately.
The folder will contain the new ODB, the jar file, and the 4 db files (with no extensions). The files have to be kept together but the folder is portable.

When you open the database, the footer in Base will show JDBC | hsqldb:file:///… instead of Embedded, indicating an external database.

If you appropriately use Medium security, to avoid accidentally approving macros in an unknown document, you’ll have to OK the macros each time you open the database. To avoid this, you’ll want to add the location of your new database folder to “Trusted file locations” in Security, Macro Security, Trusted Sources tab.

Happy Data!

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at
Entries and comments feeds.