The built-in database tool in OpenOffice.org 2.0 is one of the most eagerly-awaited features in the new release. I know a lot of people were also eagerly awaiting the ability to convert Excel macros to OpenOffice.org macros but, unfortunately, those people are still waiting. Right now, let's take a tour of the database tool.
I'm not a lifetime database devotee, but I do know my way around the different data types -- string, integer, etc.-- and used to be the tech report writer for Great Plains Dynamics. I've also been known to discuss CLOBS and BLOBS; though, never in mixed company.
|Solveig Haugland, Site expert|
So, let's get going on my overview of the OpenOffice 2.0 database tool's new features: the database, the forms, mail merging in from those databases, and, generally, how easy it is to use. Since this is a huge topic, I'm going to be covering this in several articles that will be published over the course of the next two months.
In this article, I'm going to go over the basics of creating a database from scratch. With that comes creating tables as well as making a simple data entry form as another way to get information into the database.
There are a host of other complex, juicy topics that I'll get into in subsequent articles, such as queries, reports, the ever-present limitations of spreadsheet-based databases, linking to existing data, linking fields in separate databases or tables of the same database, and more.
So, you want to make a database from scratch?
Let's say your data doesn't exist yet; you're going to be entering it in the future when you start running your bed-and-breakfast or consulting business. Or you have so little that you don't mind typing it in again. At any rate, you're ready to create a fresh new database from scratch.
It's really not too difficult. OpenOffice 2.0 has a nice, new wizard interface that's not overly hand-holding, as some of the mail merge wizards have been.
Step 1: Creating the database
It all starts logically enough with File > New > Database. (As a long-suffering user of "Insert > Envelope," I'm always excited when you can create something new by choosing File > New.)
You get this window, where you choose New Database. Select Create a New Database and click Next. The options are pretty self-explanatory.
Follow the wizard all the way through to the end. Don't choose Finish prematurely in this window or any of the other windows.
Save the database using some appropriate name like customers_database.
You'll see the main database configuration window, where you can create tables, queries, forms, and reports.
You don't actually have any data yet, so the next step is to create at least one table.
Step 2: Creating a table
You can create lots of tables for each database. I'm just going to create one for my customer's database.
In the main database configuration work area, select the Tables icon on the left. You can create a table with the wizard or in design view.
If you're a techie type, you might enjoy the design view.
That's a cool view, but I'm sticking with the basics this week; so, I'll walk through how the wizard works. When I double-click the Use Wizard to Create Table option, I get the first window of the wizard. You get a whole bunch of default fields you can use, in multiple sample tables: Employees, Expenses, and more. You can pick and choose, using different fields from the different sample tables. The options here are to provide a quick way to enter an assortment of related fields, such as employee addresses or invoice information and also to give you default data values. Names are always character values, etc.
Select your sample tables, select a field or ten from each, and use the arrows to put them in the Selected Fields list. Be sure to put them in the order you want them, in the Selected Fields list.
Click Next, and the next window lets you change the default data structures, if you're so inclined. It's pretty much a no-brainer; if you cared about control of these values, you'd be in design view. Look at them to make sure they meet some criteria of what you want, then click Next.
The next window lets you choose the primary key. Note that the default value is to create a separate field that is autoincremented and functions only as the primary key. If you like that, great; if not, choose to use an existing unique field or field combination like CustomerID.
In the proceeding window, you will name the table, something like Customer Names, and save it. If you leave Insert Data Immediately and click Finish, you'll get the window where you can enter as little (none) or as much (lots) data as you want. Here's what it looks like.
You'll notice that I didn't put the fields in the right order when I set up the table; Address is the first field. But I'm passing the fruits of my mistakes onto you. Always put your fields in the right order when you create tables; it's helpful here and when you create data entry or data viewing forms.
Start typing away. As you get to the end of the row, you can either click the Save icon on the object bar at the top left to save, or just press Tab and the row will be saved. This means you can close and not worry about saving; it's already saved.
I entered a few rows of data and closed the table. It's listed now in the main database configuration window. You can see the data if you choose document from the dropdown list at the right side of the work area.
Tables are great. But getting more data in there....you're going to do it eventually, and it would be nice not to have to allow all your users to get into the table configuration window to do so. A data entry form would be convenient.
Continue on to part two for the rest of this how-to, and you'll find instructions for creating a data entry form and entering data.
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 OpenOffice.org 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 OpenOffice.org 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.