Difference between revisions of "Documentation/OOo3 User Guides/Getting Started/Using data sources"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Accessing a dBase database)
Line 24: Line 24:
 
{{Documentation/Note|Clicking the ''New'' icon and ''Database'' in the drop-down menu also opens the ''Database Wizard'' window.}}
 
{{Documentation/Note|Clicking the ''New'' icon and ''Database'' in the drop-down menu also opens the ''Database Wizard'' window.}}
  
<li>Select '''Connect to an existing database'''. Pressing the ''TAB'' key highlights the ''Database type'' drop-down list. Typing ''D'' selects ''dBase''. Click '''Next'''.</li>  
+
<li>Select '''Connect to an existing database'''. Pressing the ''Tab'' key highlights the ''Database type'' drop-down list. Typing ''D'' selects ''dBase''. Click '''Next'''.</li>  
  
 
{{Documentation/Note|Clicking the arrows opens a menu from which you can select ''dBase''.}}
 
{{Documentation/Note|Clicking the arrows opens a menu from which you can select ''dBase''.}}
Line 32: Line 32:
 
<li>Click ''Browse'' and select the folder containing the database. Click '''Next'''.</li>
 
<li>Click ''Browse'' and select the folder containing the database. Click '''Next'''.</li>
 
<li>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.</li>
 
<li>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.</li>
<li>Create the ''Form'' using the ''Form Wizard'' as explained in [[Getting Started: Creating a database form]].</li>
+
<li>Create the ''Form'' using the ''Form Wizard'' as explained in [[Documentation/OOo3 User Guides/Getting_Started/Creating a database form|Creating a database form]].</li>
 
</ol>
 
</ol>
  

Revision as of 02:19, 12 October 2008



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.

Template:Documentation/Tip

Documentation caution.png 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.
  2. Template:Documentation/Note

  3. Select Connect to an existing database. Pressing the Tab key highlights the Database type drop-down list. Typing D selects dBase. Click Next.
  4. Template:Documentation/Note

    Database type selection.
  5. Click Browse and select the folder containing the database. Click Next.
  6. 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.
  7. 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.
Documentation caution.png 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

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:

  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.

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.

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.

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

To insert a field from a table into a document (for example a Calc spreadsheet or 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). In Calc it will appear as a text box.

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 covered in Chapter 11 (Using Mail Merge) in the Writer Guide.

Top of page

Entering data in a form

Records are used to organize the data we enter into a form. They also organize the data we enter into a subform.

Single Record.

Each type of field allows a different method to enter the data. In most if not all cases, more than one method can be used.

The first step to entering data in a form is to open it from the main database window.

  1. Click the Forms icon in the Database list.
  2. Find the form's name in the Forms list (Vacations).
  3. Double-click the form's name.

The quickest way to enter a date in the Date field is to click the arrow that opens the dropdown calendar. Then click the date you want. Use the Tab key to go to the Odometer field.

  1. Click the left arrow before May to go back one month (April 2007).
  2. Click the right arrow after 2007 to go forward one month (June 2007).
  3. Multiple clicks in either direction will change the month and year the same number of times as the number of clicks.
Calendar dropdown.

The Odometer, Tolls, and Motel fields are numerical fields. Enter values directly into them, or use the up and down arrows. When the value is entered, use the Tab key to go to the next field.

  • The up arrow increases the value, and the down arrow decreases the value.
  • These two arrows only change the numerals to the left of the decimal place.
  • Numerals to the right of the decimal place must be changed by deleting them and typing the desired ones.

The Motel's Payment field is a dropdown list. If as in my case, all of the elements of the list start with different letters, typing the first letter will select the desired letter.

  • If two or more elements of the list have the same first letter, repeated typing of the first letter will cycle through the elements with this same first letter. So, if you accidentally go past the the element you wanted, you can keep typing the first letter until it reappears again.
  • When the selection is correct, use the Tab key to go to the Misc. field.

The rest of the fields of the main form are either numerical fields or dropdown lists until we reach the Misc. Notes field. It is a text field. Type anything you desire in this field just as you would any simple text editor.

Documentation caution.png Since the Tab key is used to move between fields, it can not be used in a text field. All spacing must be done by the spacebar. Finally, the Enter key only acts as a line break to move the cursor to the next line.

Template:Documentation/Note

Since we have a subform form, using the Tab key places the cursor in the first Date field of the subform with the date automatically entered to match the Date field of the main form.

The FuelCost, FuelQuanity, and Odometer fields are numerical fields. The Payment field is a dropdown list. Enter the data just as you did in the main form, and use the Tab key to go to the next field.

When you use the Tab key to leave the Payment field, it goes to the Date field of the next line and automatically enters the date. Now you can enter your second set of fuel data for this day.

To move to another record when the form has a subform, the directional arrows at the bottom must be use. There are four of them from left to right: First Record, Previous Record, Next Record, and Last Record (Figure 64). To the right of these arrows in the New Record icon.

To create a new record while in another record, click the New Record icon. Two choices exist for creating a new record following the present record:

  • Click the Next Record icon.
  • Or, click the New Record icon.
Navigation arrows of a form.

Template:Documentation/Tip

Below is a record with data inserted in its fields. Note that not all fields have data in them. It is only necessary to have data in every field if you determine ahead of time to require all fields contain data.

Sample record of the Vacation form and subform.

Top of page

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools