Accessing other data sources

Getting Started with OpenOffice.org 3

Chapter 8. Getting Started with Base

< Previous Section

Next Section >

• Planning a database - DRAFT
• Creating a new database
• Creating database tables
• Creating a View
• Defining relationships
• Creating a database form
• Accessing other data sources
• Entering data in a form
• Creating queries
• Creating reports

< Previous Page

Next Page >

OpenOffice.org allows data sources to be accessed and then linked into OOo documents. For example, a mail merge links an external document containing a list of names and addresses into a letter, with one copy of the letter being generated for each entry.

To register a data source, choose File > New > Database to open the Database Wizard. Select Connect to an existing database. This allows access to the list of data sources that can be registered with OOo. These data sources can be accessed similarly to a dBase database as explained in the next section.

Once a data source has been registered, it can be used in any other OOo component (for example Writer or Calc) by selecting View > Data Sources or pressing the F4 key.

Mozilla Address Books and dBase databases (among others) can be accessed, and entries can be added or changed.

Spreadsheets can be accessed, but no changes can be made in the spreadsheet entries. All changes in a spreadsheet sheet must be made in the spreadsheet itself. Update the database and save it. Afterwards you see in your database the changes you made and saved in the spreadsheet. If you create and save an additional sheet in your spreadsheet, the database will have a new table the next time you access it.

Top of page

Accessing a dBase database

1. File > New > Database opens the Database Wizard window.

Clicking the New icon and Database in the drop-down menu also opens the Database Wizard window.

2. Select Connect to an existing database. Pressing the Tab key highlights the Database type drop-down list. Typing D selects dBase. Click Next.

Clicking the arrows opens a menu from which you can select dBase.

File:DB-connect.png

Database type selection.

3. Click Browse and select the folder containing the database. Click Next.
4. Accept the default settings: Register the database for me, and Open the database for editing. Click Finish. Name and save the database in the location of your choice.
5. Create the Form using the Form Wizard as explained in Creating a database form.

Top of page

Accessing a Mozilla address book

Accessing a Mozilla Address Book is very similar to accessing a dBase database.

1. Select File > New > Database.
2. Select Connect to an existing database. Select Mozilla Address Book as the database type.
3. Register this data source.

These are steps 1, 2 and 4 of Accessing a dBase database.

Top of page

Accessing spreadsheets

Accessing a spreadsheet is also very similar to accessing a dBase database.

1. Select File > New > Database.
2. Select Connect to an existing database. Select Spreadsheet as the Database type.
3. Click Browse to locate the spreadsheet you want to access. If the spreadsheet is password protected, check the Password required box. Click Next.
4. If the spreadsheet requires a user's name, enter it. If a password is also required, check its box. Click Next.

This method of accessing a spreadsheet does not allow you to change anything in the spreadsheet. All modifications must be made in the spreadsheet itself. This method only allows you to view the contents of the spreadsheet, run queries, and create reports based upon the data already entered into the spreadsheet.

Top of page

Registering databases created by OOo2.x or later

This is a simple procedure. Tools > Options > OpenOffice.org Base > Databases. Under Registered databases, there is a list of these databases. Below this list are three buttons: New, Delete, Edit. To register a database created by OOo2.x or later:

1. Click New.
2. Browse to where the database is located.
3. Make sure the registered name is correct.
4. Click OK.

Top of page

Using data sources in OpenOffice.org

Having registered the data source, whether a spreadsheet, text document, external database or other accepted data source, you can use it in other OpenOffice.org components including Writer and Calc.

Top of page

Viewing data sources

Open a document in Writer or Calc. To view the data sources available, press F4 or select View > Data Sources from the pull-down menu. This brings up a list of registered databases, which will include Bibliography and any other database registered.

To view each database, click on the + to the left of the database's name. (This has been done for the Automobile database shown below.) This brings up Tables and Queries. Click on the + next to Tables to view the individual tables created. Now double-click on a table to see all the records held in it.

File:Databases.png

Databases.

Top of page

Editing data sources

Some data sources can be edited in the View Data Sources dialog. A spreadsheet can not. A record can be edited, added or deleted.

The data is displayed on the right side of the screen. Click in a field to edit the value.

Beneath the records are five tiny buttons. The first four move backwards or forwards through the records, or to the beginning or end. The fifth button, with a small star, inserts a new record.

File:ViewDSnav.png

View Data Sources navigation buttons.

To delete a record, right-click on the gray box to the left of a row to highlight the entire row, and select Delete Rows to remove the selected row.

File:DelRowDV.png

Deleting a row in the Data View window.

Top of page

Launching Base to work on data sources

You can launch OOo Base at any time from the View Data Source pane. Just right-click on a database or the Tables or Queries icons and select Edit Database File. Once in Base, you can edit, add and delete tables, queries, forms and reports.

Top of page

Using data sources in OOo documents

Data can be placed into Writer and Calc documents from the tables in the data source window. In Writer, values from individual fields can be inserted. Or, a complete table can be created in the Writer document. One common way to use a data source is to perform a mail merge.

Selecting Tools > Mail Merge Wizard or clicking on the Mail Merge icon (a small paper-and-envelope icon on the View Data Source pane) launches the Mail Merge wizard which steps through creating a mail merge document. This is discussed in Chapter 11 (Using Mail Merge) in the [[../../Writer Guide|Writer Guide]].

Writer documents

To insert a field from a table opened in the data source window into a Writer document, click on the field name (the gray square at the top of the field list) and, with the left mouse button held down, drag the field onto the document. In a Writer document, it will appear as <FIELD> (where FIELD is the name of the field you dragged).

For example: enter the cost of meals and who paid for them on a certain date of a vacation. Make a sentence of this data: “On (date), our breakfast cost (amount) paid by (name), our lunch cost (amount) paid by (name), and our supper cost (amount) paid by (name). Write the words of the sentence with the exception of the words in parentheses. Use the correct field names in the place of the words in parentheses.

1. Replacing (data)
   • Begin the sentence by typing the word On.
   • Click the field name Data in the data source window and drag it to the right of the word On.
   • The sentence becomes: On <Date>. If you have Field shadings turned on (View > Field shading), <Date> has a gray background. Otherwise it does not.
2. Replacing first (amount)
   • Continue typing after <Date>: our breakfast cost.
   • Click the Breakfast field name and drag it to the right of what you have just typed.
   • Make sure you have the proper spacing between the field names and the words before and after them.
   • Results so far: On <Date> our breakfast costs <Breakfast>,
3. Replacing the first name:
   • Continue typing after <Breakfast>: paid by making sure to add a space afterward.
   • Click the Bpayment field name and drag it to the right of what you just typed.
   • Place a comma after <Bpayment>.
   • Results so far: On <Date> our breakfast cost <Breakfast> paid by Dan<BPayment>,
4. Follow these examples to fill in the rest of the fields in the sentence.
   • Use <Lunch> and <Lpayment> for the second set of (amount) and (name) in the sentence.
   • Use <Supper> and <Spayment> for the third set of (amount) and (name) in the sentence.
   • Final results: On <Date> our breakfast cost <Breakfast> paid by <BPayment>, our lunch cost <Lunch> paid by <LPayment>, our supper cost <Supper> paid by <SPayment>.
5. Add data to the fields of the sentence:
   • Click the gray box to the left of the row of data you want to add. That row should be highlighted like the second row of figure below.
   • Click the Data to Fields icon (circled in black in figure below).This should fill the fields with the data from the row you chose.
   • Click another row and then click this icon again. The data in the sentence changes to this selected row of data.
6. Save the document.

Adding data in table format is a little easier and takes perhaps a few less steps. Some of the steps will be quite similar.

Data can be added this way as a fields or text. Following the following steps. I leave this to the reader with which to experiment by changing the selections in the Insert Database Columns to see what results you can get.

1. Navigate to the place you want to place the table and click the location.
2. Click the gray box to the left of each row of the data source that you want to be a row in your table.
3. Click the Data to text icon to open the Insert Database Columns dialog. (The Data to text icon is circled in red below.)
File:DataToSourceIcon.png
Selected row in data source window.
File:InsDBCols.png
Insert Database Columns dialog.
4. Move the fields you want in your table from the Database Columns list to Table column(s) list.
• To place the fields in the order you select, click the field and use the single arrow to move the fields in the order you desire. You can also limit the fields you use to less than all of the fields available.
• If you want to use all of the fields, use the double arrow pointing to the right to move all of them at one time. The order of the fields in the table you create will be the same as in the data source table.
• If you want to remove a single field from the Table Column(s) list, click the field and use the single arrow pointing to the left.
• If you want to start over by moving all of the fields back to the Database Columns list, click the double arrow pointing to the left.
5. Select the settings for your table. Use the default settings as in the figure above.
6. Click OK.
7. Save the document.

Calc spreadsheets

There are two ways to enter data in a Calc spreadsheet. One enters the data into the spreadsheet cells. The other creates records in the spreadsheet just like they are done in creating a form in a database. While you can directly access the data in the spreadsheet cells, you can only see the data in the records created in the spreadsheet.

Entering data directly to the spreadsheet cells uses the Data to Text icon as we did to make a table in a Writer document. But differences exist in these two situations.

The steps are straightforward.

1. Click the cell of the spreadsheet which you want to be the top left of your data including the column names.
2. Use F4 to open the database source window and select the table whose data you want to use.
3. Select the rows of data you want to add to the spreadsheet:
   • Click the gray box to the left of the row you want to select if only selecting one row. That row is highlighted.
   • To select multiple rows, hold down the shift key while clicking the gray box of the rows you need. Those rows are highlighted.
   • To select all the rows, click the gray box in the upper left corner. All rows are highlighted.
4. Click the Data to text icon to insert the data into the spreadsheet cells.
5. Save the spreadsheet.

Adding records to a spreadsheet is fairly easy. You need to have the Data Source window open, your spreadsheet open, and the table you want to use selected.

1. Click the gray box above the field name you wish to use to highlight it.
2. Drop and drag the gray box to where you want the record to appear in the spreadsheet.
3. Repeat until you have moved all of the fields you need to where you want them.
4. Close the Data Source window: use F4.
5. Save the spreadsheet and click the Edit File button to make the spreadsheet read only. All of the fields will show the value for the data of the first record you selected.
6. Add the Form Navigation toolbar: View > Toolbars > Form Navigation.
7. Click the arrows on the Form Navigation toolbar to view the different records of the table. (The arrows are circled in red.) The number in the box changes when you change the record number by clicking an arrow. The data in the fields changes correspondingly to the data for that particular record number.

File:NavArrows.png

Navigation arrows of a form.

Top of page