Problem solve Get help with specific problems with your technologies, process and projects.

Connecting to existing databases in 2.0

In OpenOffice 2.0, how do you link to the perfectly good data you've got lying around? Here's how to do it, even if your data is living in Access, and it's not that hard.

In the glamorous world of office suites, the new features often get all the attention. The Bases, the Impress Redux and the New Toolbar Metaphors of 2.0 get all the cover stories and the center spreads in Office Suite Weekly, while the slight changes that make life easier often get ignored.

It's a sad story.

I'm here to change that; a bit, at least.

Creating a new database with OpenOffice 2.0 Base is convenient. You get to start from scratch, use cool features like relations and data entry forms and so on. I sure wouldn't argue against anyone using Base.

However, few of us come to the table empty-handed, data-wise. We, at least, have an address book lurking somewhere. We probably have a spreadsheet of items such as a list of customers, all our expenses for the last year, all the Star Trek conventions we want to go to and who the headliners are, etc. And there's always the possibility that one, or two of us have an Oracle or Access database and that we might even like to use it with our office suite.

So the main question is, in 2.0, how do you link to the perfectly good data you've got lying around? The answer is: it's very similar to how you used to do it, just easier. That's always a plus.

Connecting to an existing data source to make an 2.0 database

In 1.x, connecting to the source of existing data wasn't an intuitive process, but it wasn't hard once you had it down.

Now, there's a new approach. Luckily, the new approach is even simpler. It all starts with that beautiful phrase: "File > New." In this case, very logically, it starts with File > New > Database. That's the same way you'd start creating a database from scratch in the new Base database tool, too. So, there's just one starting point.

When you choose File > New > Database, you get this window.

All you have to do in this window is say what kind of file your data is in: text, spreadsheet, address book or another database type such as Access. There's no problem there.

Click Next. What you get at this point depends on what type of data you choose.

  • If you chose Spreadsheet, just point to the spreadsheet on your hard disk. Every sheet becomes a table in the spreadsheet, just like in version 1.x. That's it.
  • If you chose Text, you have similar choices to the ones you made in 1.x. You point to the directory where the text file is, and all the text files in that directory become tables, one table for each file. Then you specify whether the file is a plain text file or comma-separated. Do tabs separate the fields or something else? That's all the same, though.
  • If you choose Address book, it depends. With a Mozilla (Netscape, etc.), Windows, Thunderbird or Outlook address book, you do absolutely nothing. The system finds it. With an LDAP address book, that's more complicated, as is appropriate. Generally, you fill in a few server-related fields
  • .
  • If you chose Access—you specify the name of the database.

Once you've specified information about the data, click Next. You'll see a window asking if you want to register the database, and if you want to open it for editing. You must register the database (the default) but you don't have to open the database for editing (editing it is the default). However, you certainly can open it for editing; it's useful to see the main database window.

Then click Finish, and save the database under a name and location of your choice.

It's like creating a database in Base from scratch.

Notes and caveats

Here are a few thoughts on using this approach:

  • All the complexity of your database applies to using it in If you encounter issues, troubleshoot the database, as well as the connection in
  • To add data to an address book, text file or spreadsheet database, add the data to the document, not through the interface.
  • Relations aren't supported for databases based on text files, address books or spreadsheets. However, queries are fine.
  • Make sure that the document or database you've connected to is closed before you try to access it in
  • Remember that with queries and views, you can keep your spreadsheet or other source of data complete, then narrow it down to get exactly what you want.

The database connection feature: A big improvement

I'll say this several million times before I die, but I love logical navigation. It's wonderful to be able to put this feature under File > New > Database and let the wizard window do the branching out into new, as opposed to connecting to, existing data.

The mysterious (ellipsis) browse button from 1.x has been replaced, too, and now has a button with a word on it (Browse). That make it a lot easier to figure out. This is another feature, along with the total rewrite of Impress, that I recommend showing to people who might have initially been resistant to

The new features might be more glamorous -- say, the Angelina Jolie of the new release -- but don't overlook this plain, functional, logical rewrite.

Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.

Dig Deeper on Linux servers