OpenOffice.org database application: A first look review

Soon OpenOffice.org will introduce a database application to its office suite. For the last couple of years, OpenOffice.org has been developing its word processor, spreadsheet, and presentation manager programs to compete head-to-head with similar software products such as MS Office, Koffice, and Textmaker. The upcoming Version 2.0 rounds out the offering and adds the ability to create standalone databases, associated forms, reports, and queries much like MS Access. According to the documentation it’s called OpenOffice.org Base.

This story will discuss the OpenOffice.org Base and how it can be used in your business. I’ll touch on the similarities (plus differences) with the OpenOffice.org database program and Access. I’ll continue with a little commentary on migration issues. We’ll finish up by building a basic contact database, a form, a query, and a report using the wizards. If you are already an OpenOffice.org believer, you can just skip down to the “build a quick database application” section to see how it works.

If you’ve been thinking about trying OpenOffice.org, read on and I’ll give you some ideas on how this new feature can help your business.

Why a database program in OpenOffice.org?

According to the specification document (in OpenOffice.org .sxw format) referenced on the OpenOffice.org 2.0 Office Suite Guide to New Features.

“One of the most annoying things is that people ask on the OpenOffice.org lists, ‘Does OpenOffice.org also support databases?’. This has been the case since the first release. The problem is that the database part of OpenOffice.org was not that intuitive to find and the approach of data sources was hard to understand. Another point is that the current implementation is too development specific resulting in the normal user not recognizing the intention of the links in a data source. This has to be changed.

The second point, which is also very important, is that people often ask for the same functionality as found in a major competing product. In fact, most of the features that our competitors provide are already implemented in OpenOffice.org, but as one OpenOffice.org user wrote, these remain a ‘hidden treasure’.”

Apparently users have wanted a standalone database application for quite some time.

Less apparent, is the fact the OpenOffice.org has connectivity to databases already built in. Take a look at my “Plug OpenOffice.org into PostgreSQL” article from July 2004 for a rundown on connecting OpenOffice.org Writer to PostgreSQL tables. Much of the difficulty seems to have been that most users were not aware of the existing connectivity or how to effectively use it in their businesses. Let’s face it, making the jump from database table to a pseudo application implemented in Writer, isn’t really all that intuitive.

The new Base application is the first step in making connectivity between OpenOffice.org and databases easier.

There are several ways you might use the new application in your business. Windows users will recognize the opportunity to whip up a quick little database application for small jobs. I used to use an Access database with some forms on a Windows machine to verify a billing system. When we got to the point where the billing balanced and was stable, we phased out using the Access database. Quick, simple, cheap, and it did the job.

After OpenOffice.org 2.0 is released, I’ll probably try designing small production databases in the new program. It will be comforting to know that I’ll have the reliability of a Linux machine. Automatic backups to a central server is also fairly painless on a Linux box, so data loss issues can be virtually written off.

Another way to use the new program could be to quickly build a prototype system for ironing out business processes in preparation for a full fledged enterprise level client/server database project. How many times have you done that? Now you can start.

MS Access meets OpenOffice.org Base

OpenOffice.org Base has a similar look and feel to Microsoft Access. Both have graphical interfaces, wizards, and sample data to get you started.

Screen shot of the main database screen.

Both applications can run on like hardware, as well. Access requires a minimum 233 Mhz Pentium box (a Pentium 3 processor is recommended) and at least 128 MB of RAM. It also requires Windows 2000, XP or later versions of the Microsoft operating system.

I’ve run current versions of OpenOffice.org on old 133 Mhz desktops. Probably not something you’d do in a big company although it does give you a good idea of the compatibility. I’m happy to say that this pre-release version (1.9.5) runs fine. There’s been noticeable disk drive activity when loading the program, opening a file, and moving between desktop screens. I suspect that some of that thrashing has to do with debugging information being logged, but I’m not sure. I’m confident, things will be cleaned up with the production version.

Other similarities include the database file format and the capability of accessing external data.

Both Access and OpenOffice.org Base use some form of XML to store their data. I did take the example rob10.odb and unzip it into its own directory. What I ended up with were various files, like content.xml, settings.xml, and mime types. A couple of directories were generated, like database, forms, and META-INF. XML slingers will undoubtedly know about these things.

Of course, one difference between Access and OpenOffice.org Base is the cost. According to the Microsoft Web site retail pricing for Access 2003 is $229 for new users and $109 for upgrades. Naturally, OpenOffice.org is available on the Web. You can download a version for Windows or Linux and just use it. Feel free to contribute to the OpenOffice.org project via cash or time.

Should you migrate?

It’s great that OpenOffice.org has incorporated a new database application in their office suite. I’m sure many readers will be a little apprehensive about moving from Access to OpenOffice.org Base, especially since it’s the first release.

According to the specification document, the only data that can be imported are forms and reports. Also, there is currently no way to export anything, such as a table to a file. The documents did mention that when import/export capability is added that it should be to an XML native format.

During my testing, I was also unable to get any connectivity to spreadsheets or other databases to work. I could see the properties, connection types, and advanced settings menu items under the Edit and Database tabs, but they were unlit. And even though I could select Mozilla address books from my localhost, I was unable to complete a connection, there either.

Screen shot of the data entry (spreadsheet) screen.

We should remember that this is a pre-released version of the software. It’s unreasonable to expect everything to work properly, at this point. The basic application performed well, otherwise.

For readers considering a switch away from the proprietary Access package, this might be a good time to work with the OpenOffice.org Base system. Getting in early and helping iron out the bugs is also another option. Projects always need testers and feedback. Don’t forget that OpenOffice.org is available for the Windows platform, as well. If you need this type of tool in your business, it might make sense to be in on the ground floor, since it is open source, open format, and freely downloadable. Learn a little as you go and add back into the code to make it better. Don’t forget to mention, in your marketing, that you are a supporter of OpenOffice.org.

We’ve covered the new application and some of the ways you might use it in your business. We’ve also considered a few similarities between the OpenOffice.org Base application and Microsoft Access. And, we’ve looked at migration issues.

It’s finally time to see the program in action. Let’s build a basic contact database system.

Build a quick contact database

Building a quick database application couldn’t be simpler. You can follow along as I go through the steps. You’ll notice that the process mirrors the Microsoft Access approach almost exactly.

Create the database

To create a sample table and data follow these steps:

• Start a new database from the File tab within OpenOffice.org.
• Click the Create a New Database button on the Table Wizard screen, followed by Next.
• On the Save and Proceed screen, click Finish.
• On the main database screen (see figure 1), highlight the Table button, then select Use Wizard to Create Table.
• Select the Business category and use the contact table of fields.
• Double left click each field that’s desired in the new table, followed by Next.
• On the Set Field Types and Formats screen I change the Contact ID field type to text, followed by Next.
• Click the Create Primary Key and automatically add buttons, followed by Next.
• On the Create Table screen choose Contacts for the table name and click on the Insert Data Immediately radio button.
• The table is then created and you should be able to enter some sample records (see figure 2) into the spreadsheet style data entry screen that appears. Next close the table using that command under the File tab.

Create a form

Entering data in an office or production environment is much nicer if you use a form. Data entry can then be done quickly with the tab key or the mouse. Here are the steps:

• On the main database view select the Contact Wizard button to bring up the next screen.
• Select Contacts from the table and query selection box.
• Use the >> box to select all the fields in the table, followed by Next.
• Bypass subforms by clicking Next on the Subform screen.
• Chose the Columnar – Align Left button under the Arrange Controls screen, followed by Next.
• Click Next on the Set Data Entry screen.
• On the Apply styles screen use the Bright Blue style and the 3D look, followed by Next.
• Name the form Contacts and use the Finish button to get back to the main database screen.

Forms are nice for data entry and queries let you find and group data. Let’s build a query next.

Build a query

Queries are used for viewing, sorting, and filtering of data. It’s easy to do a query, here’s an example:

Select the Query button on the main database screen

Click on the Use The Wizard To Create Query selection to open the Query Wizard screen

On the Field Selection step choose the Contact table and >> button to select all the fields

Click Next to go to the next screen

Select Contacts.LastName in the sort by field, followed by Next

Click Next to move through the Search conditions screen

Click Next to move through the Detail or summary screen

Click Next to move through the Aliases screen

Click Finish on the Overview screen

After OpenOffice.org has built your query, you’ll see a spreadsheet style data screen with the data sorted alphabetically according to the last name field. Close the data screen with File and close. To run the query, double left click the Contacts query.

Create a report

The Wizard again takes all the work out of designing a report.

Click the Reports button and then select the Use Wizard To Create Report task.

On the Field Selection screen choose the Contacts table and click the >> to select all the fields.

Click Next to advance.

Click Next on the Labeling Fields screen.

Select LastName and ContactID on the Groupings screen, followed by Next.

Select Sort By LastName on the Sorting options screen. Click Next.

On the Choose layout step choose Outline – Compact for the Layout of data and default for the Layout of headers and footers. Use next to advance.

Call the report Contacts, select static and then click Finish.

You’ll be greeted with a report showing your ID, contact id, lastname, firstname, etc. neatly arranged in columns.

The report can be closed using File and close. Double left clicking the Contacts report on the main database screen runs it again.

We’ve just created a basic example database application. You can try using different options on the various screens and data.

Wrap up

The new OpenOffice.org Base product has a lot of potential. It will take some time to mature, although if it’s anything like other OpenOffice applications, the fixes and enhancements will come swiftly.

I actually used OpenOffice.org version 2.0 (pre-release 1.9.5) and the Base application to write this story. How’s that for trust. No major headaches and as you can see, I was able to successfully complete my work.

Look for the production version sometime in the near future.

In the mean time, give the database application a try, either on Linux or under Windows. It looks like it will be a good product.

Rob Reilly is a consultant, writer, and commentator who advises clients on business & technology projects. His Linux, portable computing, and public speaking skills-related articles regularly appear in various high-end Linux and business media outlets. Send him a note or visit his Web site.