Open Office #openoffice #oobase #oocalc #odbc

Open Office has a database program (Open Office Base) and a spreadsheet program (Open Office Calc). Base can be connected via ODBC to a SQLite database in much the same way as has been described for Microsoft Access, using the same SQLite ODBC driver (See Part One of MS Access Engine). A .odb file is created, saving the connection information and stored SQL queries. For later access to the SQLite database, one can open the .odb file with Base.

Additionally, like MS Office, one can register the .odb database with Open Office so that its tables and queries can be accessed in text and spreadsheet documents using Open Office Writer and Calc, respectively.

Base has a graphic query design tool similar to that of MS Access as well as a direct SQL editor.

Single queries that have been developed using other SQLite managers work in many cases when copied into the Base SQL editor and run using the RUN SQL Direct mode; some things that do not work:

  • multiple SQL commands in the one query
  • runtime input parameters
  • comment lines before the first SQL word
  • semi-colon (;) and apostrophe or single quote (‘) in comment

With the RUN SQL Direct mode turned off, most queries that work in other SQLite managers do not work when copied into the SQL View and run. I think in this mode, the commands are syntax which may be limited to a subset of the SQLite command set. It is not readily apparent what it is that allows a query to work in the direct mode and not in the OpenOffice mode. The error messages are useless and give no clue where to look.

Extracting the query from AllFacts4Persons9.sql to return all events for the individual worked “out-of-the-box”, complete with very complex date processing, in the Run SQL Directly mode. Opposingly, that query at the foundation of shared events for a couple would not work as it comprises multiple SQL commands. I have attempted to rebuild it using the SQL Design View method and can get it to work without processing the event date field but, using this method, there seems to be no way of incorporating the complex CASE structure employed for date processing. Simply pasting that part into the code generated by Base throws up untraceable errors. Probably the SQL CASE expression is unsupported or is different in the syntax.

Base SQL Design View produces what we would call “bloated” code – double quotes around table names and field names, for example, and no short alias for a long table name. It has the virtue of being able to query not only tables but also queries stored previously in the OOBase database. The SQL direct mode cannot query stored queries while the indirect mode does not support UNION.

The inability to incorporate the querying of queries, complex expressions and the UNION operator in one mode makes it very difficult to accomplish in OOBase what one can do outside of it. Moreover, OOBase’s filtering and sorting functions for results work only in the indirect or native mode, not in the SQL direct mode – another shortcoming.

The connection from OOCalc to a registered database can help. Queries and Tables can be selected and results viewed in the Calc viewer. Results can be dragged onto a spreadsheet and filtering applied there. Even more directly, a query can be selected from the Base Queries list to a Calc spreadsheet and the results will populate the sheet.

Unfortunately, OpenOffice and the ODBC connection to the RootsMagic 4 SQLite databases is so limited and failure-prone that it cannot be recommended and prospective users are warned of the prospect of frustration and disappointment. Hence, no further detail is forthcoming from this author on how to use set up and use OpenOffice with SQLite.

Discussions & comments from Wikispaces site


Open Office Base connection to Rootsmagic data base

18 April 2010 00:55:04

I’ve tried to create the connection alluded to, but have not been successful.
Could someone please provide the detailed instructions necessary to do this.


18 April 2010 15:12:21

I revised the page, not to provide any further detail but rather to warn you and others away from expecting much out of an OO connection to RootsMagic.

In any case, once you have installed the ODBC driver, you use
Control Panel > Admin Tools > Data Sources (ODBC)> ODBC Data Source Administrator
Add a User Data Source > Select SQLite3 ODBC Driver > SQLite3 ODBC DSN Configuration
Browse to/Select the RM4 file, assign a DSN name, accept the rest of the field defaults, OK
OK to close ODBC Data Source Admin


open OOBase,
File>Open to Database Wizard,
1. Connect to an existing database > select ODBC
2. Set up ODBC connection > Browse > Data Source > select the DSN you named above

You can then save a file from OOBase that has all these settings in it for later re-opening from the File>Open menu.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.