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.