Data Management

Performing common Access tasks in OpenOffice.org 2.0's Base

If you want to put together an adhoc database to track information in your organization, you may not want to shell out the money necessary to buy a copy of Microsoft Access or upgrade your copy of Microsoft Office to include Access. That's where OpenOffice 2.0's Base comes in. Greg Shultz shows you how to perform basic database tasks using Base.

While OpenOffice.org has always provided database front end tools it never really contained its own database application. With 2.0, OpenOffice.org now comes with Base, which still provides an easy-to-use database manager but also provides fully integrated HSQL database engine, which stores data in XML files. Now you can create and modify tables, forms, queries, and reports using Wizards, Design Views, or SQL Views. In addition to allowing you to create databases, BASE supports many popular databases natively, including Adabas D, ADO, Microsoft Access, MySQL, as well as almost any other database through industry-standard ODBC and JDBC drivers. Base can also access dBASE files natively for simple database work. With this in mind, let's take a look at performing some common Access tasks in OpenOffice.org 2.0's Base.

Working with an existing database

If you make the move to OpenOffice.org 2.0 and Base, chances are good that you'll want to bring your existing databases with you. Fortunately, Base's Database Wizard, which you access by selecting New | Database from the File menu, will allow you to attach any database for which a current ODBC, OLE DB, or JDBC driver is available. The Database Wizard lets you choose from a host of data sources, as shown in Figure A.

Figure A:

Base's Database Wizard will allow you to access a host of database types.

However, it's important to keep in mind that converting a database from one application to another is a tricky business no matter what application you're using. When converting a database into Base, the transition won't be without its trials and tribulations and you can expect a lot of finagling in order to get things working—especially for more complex databases.

Creating a database

Even if you use your existing databases, chances are also good that you'll eventually need to create new databases. Fortunately, the Database Wizard will allow you get up and running with a new Base database easily.

When you launch the Database Wizard, you'll see the opening screen, which by default is configured for creating a new database, so you'll just click Next. On the second screen, the wizard prompts you to register the database and to decide what to do next, as shown in Figure B. The default will open the main database configuration window where you can begin manually creating a database. If you're planning on creating a simple database and want to use a canned configuration, you can select the Create Tables Using the Table Wizard check box. However, you'll have more control over your database if you leave this check box blank at this point.

Figure B:

The Database Wizard will quickly get you started on the path to database creation in Base.

When you click Finish, you'll be prompted to name your database and then you'll see the Base's main window, where you can create tables, queries, forms, and reports, as shown in Figure C.

Figure C:

Base's main window provides you with access to all the tools and features you'll need to build your database.

While the Table Wizard will automate the creation of a canned table, the Table Design window allows you to manually create a customized table for your database, as shown in Figure D. As you can see, the user interface makes it very easy to name your fields, specify the data type, and configure the data type properties.

Figure D:

The Table Design window allows you to have more control over your database table creation operation.

Once you finish your table, you'll want to create a data entry form. To do so from Base's main window, select Forms in the Database pane and then select Use Wizard To Create Form in the Tasks pane. When it comes to creating a data entry form, the wizard, as shown in Figure E, is a great place to start as it will quickly provide you with a base form that you can edit later in the Form designer if you wish.

Figure E:

The Form Wizard allows you to quickly create a basic form that is functional yet can be edited later in the Form designer.

In fact, as you walk through the wizard, the Form designer will be visible in the background and you can see how your base form is progressing. When you get to the last step in the wizard, you can choose to begin using your base form to begin data entry or you can for right to the Form designer and tweak your form layout using a comprehensive set of tools and options, as shown in Figure F.

Figure F:

When you compete the Form Wizard, you can use the Form designer to customize your form.

Creating reports

While most of the work with any database will be done electronically, there are times when you will want to get data out of the database and print it out in a report that you can distribute it to your colleagues. To aid you in this task, Base provides you with a very efficient reporting tool along with a built-in wizard.

From Base's main window, select Reports in the Database pane and then select Use Wizard To Create Report in the Tasks pane. When you do, you'll see Writer appear on the screen and then the Report Wizard will take over and you can select the table or query your report is going to be based on and then begin selecting fields to include in your report, as shown in Figure G.

Figure G:

The report wizard makes creating reports an easy operation.

As you work through the Report Wizard, you can customize the labels, group fields, and choose a sort order. You'll then get to the Choose Layout step and can select from a variety of layout options, as shown in Figure H,

Figure H:

The Report Wizard offers you a variety of layout options.

When you get to the last page in the Report Wizard, as shown in Figure I, you can choose the kind of report you want and whether to create the report or modify layout.

Figure I:

The last page in the Report Wizard contains some important options.

As you can see, you can choose between a Static or Dynamic report. In this case a Static report is essentially a one time report in that it will contain the data in the database at the time that you run the report. A Dynamic report on the other hand is a reusable report in that it will always display the data that's currently in the database each time you run the report.

If you select the Static Report option the options below Dynamic become unavailable. However, if you leave the Dynamic Report Option selected, you can then choose to modify the report layout or to simple create or run your report. If you select the Modify Report Layout option and then click Finish, the Report Wizard will close and you'll see your report layout in Writer. You can then use any of Writer's features to modify your report layout as you see fit. You can then save the report layout and close Writer.

To actually run your report, just double-click the report name in the Reports section of Base's main window. When you do, you'll see your report in Writer complete with the data you specified when you configured the report. You can then just click the Print button and print your report.

Exporting a report to PDF format

Since your Base report end up in Writer, you can easily export the report as a standard Portable Document Format (PDF) file. To do so, just click the Export Directly As PDF button on the standard toolbar, type a filename in the Export dialog box, and click the Save button. You can then copy the PDF file to any location you want.

If you'll be e-mailing the PDF, save yourself a step and select the Send | Document as PDF command from the File menu. You'll then see the PDF Options dialog box, as shown in Figure J, and can optimize the PDF file for sending via email. Writer will then launch your email application, create new message, and then attach the PDF.

Figure J:

Since your Base report ends up in Writer, you can easy to save it as a PDF file and attach it to an e-mail message all in one step.

Accessing an alternative

OpenOffice.org 2.0 now comes with a full feature database application called Base, which provides you with everything that you need to create and use databases in your business. In this article, we've taken a look at performing some other common Access tasks in OpenOffice.org 2.0's Base.

About Greg Shultz

Greg Shultz is a freelance Technical Writer. Previously, he has worked as Documentation Specialist in the software industry, a Technical Support Specialist in educational industry, and a Technical Journalist in the computer publishing industry.

Editor's Picks

Free Newsletters, In your Inbox