OpenOffice Base 2.0 vs. Microsoft Access 2003 - Part I

The Google-Sun Microsystems non-event I discussed earlier this month had one significant side-effect: I decided to download and test-drive the current release candidate (RC2) of OpenOffice[.org] (OOo) 2.0.

Although I spend a substantial part of my working days in Word 2003, I don't consider myself a Word expert. I use Excel less often and PowerPoint only occasionally. I assume that OOo Writer, Calc, and Impress 2.0 have features that satisfy the majority—or at least a significant percentage—of Word, Excel and PowerPoint users, and can open and save documents in current Microsoft Office native formats: *.doc, *.xls, and *.ppt. If not, OOo 2.0—and StarOffice (SO) 8—would be doomed to the purgatory of Office-also-rans, and the Commonwealth of Massachusetts probably wouldn't have mandated the OASIS Open Document 1.0 format that only OOo 2.0 and SO 8 support (so far).

I'm about to start on the 11th edition of my 1,500-pp Special Edition Using Microsoft [Office] Access tome for QUE Publishing, which now has close to one million English copies in print. (The previous 10 editions are: Access 1.0, 1.1, 2.0, 95, 97 (3 editions), 2000, 2002, and 2003.) Obviously, I'm interested to learn what potential effect OOo 2.0/SO 8's upgraded database (Base) feature might have on the sales of Office 12 SKUs that include Access 12 and, thus, the future success of Access books. This post is the first of what might end up as four parts:

• Part 1 - Database Access, Queries and Simple Reports (this post)
• Part 2 - Table Design, Datasheet Data Entry, and Data Import/Export
• Part 3 - Multi-Table Data-Entry Forms and Subforms
• Part 4 - Multi-Table Reports and Subreports (if possible)

Several reviewers have called OOo Base an "Access clone." Some OOo Base windows and most of the wizards I tried resemble those in Access 2000 or later. However, the term "Access clone" is a stretch—to be charitable. When the Office 12 Beta 1 version becomes available, I'll change from Access 2003 to Access 12 and update previous comparisons where necessary.

Database Access

OOo 2.0's default (embedded) database is HSQLDB 1.8.0.1, an open-source (BSD license) Java database engine based on Thomas Mueller's original Hypersonic SQL Project. Using OOo's Base feature requires installing the Java 2.0 Runtime Engine (which is not open-source) or the presence of an alternative open-source engine, such as Kaffe. The HSQLDB 1.8.0 documentation on SourceForge states the following regarding SQL conformance:

HSQLDB 1.8.0 supports the dialect of SQL defined by SQL standards 92, 99 and 2003. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Many features of SQL92 and 99 up to Advanced Level are supported and here is support for most of SQL 2003 Foundation and several optional features of this standard. However, certain features of the Standards are not supported so no claim is made for full support of any level of the standards.

OOo supports HSQLDB as an embedded database, which means that table, view, query, form, and report definitions, as well as table data, are stored as compressed (ZIPped) content.xml files in the DatabaseName.odb file. Thus OOo *.odb files are similar in concept to Jet *.mdb files and are related to Office 12's new compressed, multiple XML file structure. OOo RC2 doesn't include an HSQLDB sample database with multiple, related tables—such as Access's Northwind.mdb or SQL Server 2005's AdventureWorks—so it's not easy to test multi-table query performance with embedded HSQLDB. Creating HSQLDB tables from existing flat files (text data) isn't a piece of cake, as you'll see in Part II. However OOo Base running under Windows can attach any database for which a current ODBC, OLE DB, or JDBC driver is available. The Database Wizard lets you choose from these data sources:

Selecting ADO, and then clicking Next and Browse opens the standard OLE DB Data Link Properties dialog from which you can select an OLE DB provider and generate a SQL Native Client connection string for SQL Server 2005, such as this:

Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost

However, you receive a "The data content could not be loaded. Invalid Object Name 'TableName'." error message when attempting to open SQL Server 2005 AdventureWorks tables or change from the SQL view to design view of a query. Northwind tables added to an SQL Server 2005 instance don't throw an error, but display multiple copies of the first row. You must specify the SQL Native Client ODBC provider to handle two-part (Schema.Table) table names, which appear like this in the Tables window:

Query Design and Processing

To test OOo Base's query design and processing capabilities, I copied the query that I used to create the report in my recent "Microsoft Staff Moonlighting as Adventure Works Employees" post to the Query window's SQL pane. The only change required was to replace [Group] with "Group", as shown here:

Clicking the Switch Design View (On/Off) button open a three-pane query design window that's almost identical to Access's da Vinci toolset for Access data projects:

Registering (by saving) the *.odb file incorporates a copy of the OLE DB connection string or ODBC data-source name (DSN), query SQL statements, and other OOo objects, such as form and report definitions. Thus *.odb files for databases with attached or linked tables are similar to *.adp Access data project or Jet *.mdb files with linked tables. The query designer adds double-quotes to all table names and aliases, whether or not they're needed.

Creating a Report from a Query

I anticipated that OOo reports would be similar to reports created with Visual Studio 2005's and SQL Server 2005 Reporting Service's HTML table-based Report Designer, rather than Access's banded style. I was surprised to find that the only option in the Reports window was Use Wizard to Create Report. (Unlike the Forms window, there's no Create Report in Design View option.)

You can add grouping levels to a report, but I found grouping on the Group field resulted in a 150+ page report. Apparently, the data source must be ordered first by the grouped field; however, the Wizard's sorting option is disabled when you specify a query as the data source for the report. Sorting on the grouped field is automatic when you use a table as the report's data source, but the resulting report has a first page that only contains a header and footer only with no group data. A page break occurs after each group.

Following is an example of an edited wizard report generated from the ungrouped Adventure Works Employee Roster:

The Report Wizard lets you apply minor variations to a fixed style. However, I wasn't able to top-align the EmployeeID value with the first line of the other fields. You can make design changes to the report by a selection in the last Wizard dialog or by closing the report, right-clicking it's name, and choosing Edit.

Reports as Writer Text Documents

An alternative to OOo Base reports is to generate reports as tables, fields or text in Writer documents. This was the only method to create a report with earlier OOo versions. Open a new Writer window, press F4 to display a list of available data sources, select the data source, and drag the table or query to the document pane, which opens this Insert Database Columns dialog:

Select a text type option—Table, Fields, or Text—add the table or query columns to display, and click OK to copy the table at the document's insertion point:

The process is similar to copying and pasting an Access table or query datasheet to a Word document. The new OOo mail merge feature uses a similar approach with address and related merge data as fields.

Conclusion - Queries and Simple Reports

OOo Base's SQL entry pane and query designer appear to me to be adequate for most projects, assuming that the user has prior experience with writing SQL SELECT queries, using graphical query designers, or both. Microsoft Office's abysmal help system is better—by at least an order of magnitude—than OOo's. OOo Base error messages are cryptic, and clicking the More button offers no more useful assistance with the problem.

I'm mystified by the Report Wizard's approach to designing basic tabular reports, surprised by the lack of sorting options with query data sources, and underwhelmed by the lack of grouping capability with unsorted query result sets. I see no obvious way to add subreports, and OOo Base appears to be missing a Wizard option for creating crosstab (matrix) reports. My conclusion is that OOo Base would be adequate for mail merge and printing simple lists but not reports that have any degree of complexity. OOo Base's Report Wizard isn't close to being a clone of its Acces 2000+ counterpart. On the other hand, I might not be using the report designer window correctly. Suggestions and assistance from experienced OOo users are welcome as comments.

Bear in mind that Microsoft Office Access 2003 as a standalone SKU sells for US$174 to $229 and the upgrade SKU goes for $85 to $114. Even if you buy into OOo Writer, Calc, and Impress, you'll undoubtedly find that using Access rather than OOo Base to create database front ends will save your investment in an Access 2003 license many times over.

Alternatively, spend $99 for VB Express and use the ReportViewer control to quickly create simple tabular and crosstab reports and graphs. VB Express also simplifies creating data entry forms—compared with VS 2002/3—but Access is even simpler. The obvious advantage of the VB Express approach is that distributing the project to multiple users doesn't require the Office Developer Edition's runtime Access license.

Technorati: Databases OpenOffice HSQLDB Access SQL Server 2005 Reporting Services