Using Data Sources

Title: Using Data Sources:

Version: 1.0
First edition: November 2004
Contents
Overview..........................................................................................................................................ii
Copyright and trademark information.........................................................................................ii
Feedback..................................................................................................................................... ii
Acknowledgments.......................................................................................................................ii
Modifications and updates......................................................................................................... iii
Working with a data source (dBase example)................................................................................. 1
Registering the data source..........................................................................................................1
Adding tables in a data source.....................................................................................................1
Viewing a data source...................................................................................................................... 2
Editing the data................................................................................................................................ 2
Using queries to select records.........................................................................................................2
Creating a data source from a spreadsheet....................................................................................... 3
Database forms.................................................................................................................................3
Form creation using AutoPilot.................................................................................................... 4
Form use......................................................................................................................................4
Editing the form.......................................................................................................................... 4
Viewing / editing controls / forms properties.........................................................................4
Editing controls...................................................................................................................... 4
Adding a sub-form..................................................................................................................5
Report creation.................................................................................................................................6

Using Data Sources i

 Overview

Overview
OpenOffice.org doesn't have a database server capability (limited ability to create and work
with dBase format files), but it does have a great integrated1 client ability.
This chapter introduces the concepts and tools required to work with data sources. More
advanced topics are found in other documents, some of which are referenced from this one.
Note to Microsoft Access users: To replace Access entirely requires transferring .mdb
(Access' own proprietary format) files to another database server such as MySQL. For
information on how to do this, see: http://mdbtools.sourceforge.net/. To keep database files in
.mdb format, use ODBC to connect to the files. See
http://www.openoffice.org/FAQs/mostfaqs.html#10.

Copyright and trademark information

The contents of this Documentation are subject to the Public Documentation License,
Version 1.0 (the "License"); you may only use this Documentation if you comply with the
terms of this License. A copy of the License is available at:
http://www.openoffice.org/licenses/PDL.rtf
The Original Documentation is Using Data Sources. The Initial Writer(s) of the Original
Documentation is/are Ian Laurenson © 2004. All Rights Reserved. (Initial Writer contact(s):
hillview@paradise.net.nz. The Initial Writer contact(s) is to report errors in the
documentation. For questions regarding how to use the software, subscribe to the Users Mail
List and post your question there: http://support.openoffice.org/index.html)
All trademarks within this guide belong to legitimate owners.

Feedback
Please direct any comments or suggestions about this document to:
authors@user-faq.openoffice.org.

Acknowledgments
Some of this material is based on work by Scott Carr and Daniel Strome.

Modifications and updates

Version Date Description of Change
1.0 7November 2004 First published version.

1 The term integrated is used here to reflect that it isn't a separate application but works in with all the applications
in OpenOffice.org.

Using Data Sources ii

 Working with a data source (dBase example)

Working with a data source (dBase example)

This section is based upon a “howto” written by Scott Carr, titled “How to Create a Data
Source from Scratch”. The original is at:
http://documentation.openoffice.org/HOW_TO/data_source/data1_EN.html.
Other useful links:
Registering other data sources: http://openoffice.homelinux.org/index.php?s=38
Step by step guide: http://homepage.ntlworld.com/garryknight/linux/oodbase.html
Comparison with Access:
http://articles.linmagau.org/modules.php?op=modload&name=Sections&file=index&req=vie
warticle&artid=230&page=1
The steps in this section set up a data source in OpenOffice.org. A dBase type database will
be created as these can be created and worked on without a separate database engine.
However, registered data sources of type dBase do not have the ability to have more than one
table in a query (or SQL statement), so the tables can not be related to each other.

Registering the data source

1) From within OpenOffice.org Writer or Calc, use Tools > Data Source.
2) Click New Data Source.
3) Enter a name for your data source. This example uses “TEST” as the Data Source
name.
4) Select dBase as the data source. (We are using dBase because it allows full control
over the database on a local system.)
5) Enter a directory for the program to store the data files in. If the directory does not yet
exist, OpenOffice.org will automatically create it (checking with the user first).
6) Click Apply.

Adding tables in a data source

This section assumes that your data source allows full access to the database. Currently,
dBase is the only type that allows this type of access to files on your computer. JDBC and
ODBC can be used if you already have a database system setup like MySQL, SQL Server, or
Oracle.
1) Click on the Tables tab.
2) Click on the New Table Design button to display the Table Design screen.
3) Enter field names, for example Name (down arrow), Telephone (down arrow) …
4) Save the table: File > Save > [name the file]. This example will be TestTable.
5) Close the Table Design screen and the Data Source dialog.

Using Data Sources 1

 Viewing a data source

Viewing a data source

Data is viewed and edited using the Data source viewer, sometimes referred to as the beamer
or database beamer.
1) Display the Data source viewer by pressing F4 or click the Data Sources button in the
Main toolbar or View > Data Sources.
2) In the data explorer (on the left side of the Data source viewer), navigate to the
desired table, for example Test > Tables > TableTest. (If the data explorer is not
visible, click the Explorer on/off button – the button on the left.)

Editing the data

This section demonstrates how OpenOffice.org can be used to enter, edit, and remove values
in a data source.
Note: OpenOffice.org can not edit data source files of types text or spreadsheet in the Data
source viewer.
• To add a new record, simply type into the bottom blank row of the displayed table or
click the button that looks like an asterisk “*” in the record number status bar.
• To move between fields, use Tab and Shift + Tab. This is just like any other table so
key controls and mouse clicks work as expected.
• Editing records is simply making the change. Moving to a different record saves the
change. To cancel a change, simply press Escape.
• Deleting records: Select the record(s) by clicking to the record’s left; selected records
are highlighted, (Control and shift keys work as expected.) Then either press the Del
key or Right Click > Delete Rows. Confirm the deletion.

Using queries to select records

Using queries is a large subject area, so what follows is just a getting started guide on
creating a query. Once the query is created, use the query as your data source. Help covers
this in more detail; select the Index tab and type “queries”.
1) In the explorer window (left window showing tree-like controls) of the Data source
viewer, open the desired data source by clicking on the “+” to the left, if required.
2) Right click Queries > New Query (Design View).
3) Choose the table for your query and click Add.
4) Typically the next step is to double-click the * to add all the field names to the query.

Using Data Sources 2

 Using queries to select records

5) Double-click the field name(s) that have the data you want to limit the query to. For
example, the field name might be “Keyword” and only records containing “Friend” are
desired.
6) Un-check the visible checkbox for these criteria fields. (This is not necessary but it
saves getting the field repeated in the final table.)
7) Type the criteria into the Criterion row. For example, 'friend' or if there might be other
text in the field for the record but it must contain the word 'friend' somewhere in the
field type “LIKE '*friend*' ”.
8) Check the query by clicking the Run query button . To adjust the various panes,
click and drag pane borders to get a desired arrangement.
9) File > Save As and name the query.
10)File > Close Window.

Creating a data source from a spreadsheet

To do a mail merge in OpenOffice.org Writer from a spreadsheet, the spreadsheet needs to be
registered as a database source, so that you can display it inside the Data source viewer.
1) Make sure your spreadsheet is saved (preferably in .sxc format). The top row will be
used for field names.
2) Tools > Data sources... > New Data Source.
3) Give it a meaningful name.
4) For Connection, choose Spreadsheet.
5) Click … > navigate to the desired spreadsheet.
6) In the Tables tab, verify that your spreadsheet name has been selected.
7) Click on OK and your sheet will be usable for things like mail merge.
Note: This method can be used to register into OpenOffice.org whatever supported data source
type you already have present on your system.

Database forms
This section is based upon a document by Daniel Strome, titled “How to Link Tables Using
Named Parameters”. The full document is available for download from
http://documentation.openoffice.org/HOW_TO/index.html. (There is a section in the
document about running a macro, but this is no longer necessary.)
This section is only a very brief look as OpenOffice.org provides a full graphical user
interface as a database client. Refer to the Database Guide for more details.

Using Data Sources 3

 Database forms

Form creation using AutoPilot

It is possible to create forms from scratch using the forms toolbar, but to get an idea of what
is possible, using the AutoPilot is a good place to start.
1) File > AutoPilot > Form...
2) Follow the steps in the dialog. In the second page of the AutoPilot, as selections are
made they are immediately visible in the newly created Writer document.

Form use
When using a form a Navigation toolbar should be visible (by default across the bottom of
the window). If it is not visible, right-click any toolbar and select Navigation bar. The
controls on this form are similar to those used in Access.
Note1: Doing a find is slow because it is handled by OpenOffice.org rather than the database
server (lot of information passing). Use filters instead.
Note2: If the table is read-only then it could be because:
OpenOffice.org requires a primary key (or bookmark facility) for the table, or
OpenOffice.org hasn't implemented writing to text or spreadsheet files, or
The database driver isn't providing the required privileges – try updating the database driver (it
may be that you don't have sufficient privileges).

Editing the form

To edit the form, display the Form functions toolbar by clicking Show Form Functions on
the Main toolbar. To toggle the form so that it can be edited, click Design Mode On / Off.
Viewing / editing controls / forms properties
1) Click the Select tool then click a control.
2) Click Control Properties... on the Form functions toolbar.
3) Fields are frequently grouped with labels. When this is the case, the title bar of the
Properties window will say “Properties: Multiselection”. To ungroup the fields, Right
click > Group > Ungroup. Now select a field and see the properties.
4) To change to the form's properties, click Form properties on the Form functions
toolbar.
Editing controls
• To add a control, click a button and drag in the document.
• Many controls have an associated AutoPilot. To turn these off / on, click the
AutoPilots On / Off button.
• To change a control from one type to another: right-click the control > Replace with >
[choose the new control type].

Using Data Sources 4

 Database forms

• To change the order that the fields are accessed when entering data and pressing Tab,
click Activation order.
Adding a sub-form
A sub-form is where the records displayed in the sub-form are dependent on the record
displayed in the main form. The two tables must have a common field. For example, the main
form might be customers’ details and the sub-form the orders for that customer; the fields in
common might be a customer identity.
To add a sub-form:
1) Display the Form navigator: Click Form Navigator on the Form Functions toolbar.
2) The Form Navigator displays a tree of forms and their controls. Click on the form
(second level - maybe called “Standard”).
3) Right click on the form > New > Form.
Note: It is very important that it is a sub-form in the hierarchy, otherwise this will not
work.
4) Optionally rename the sub-form.
5) With the sub-form selected in the Form navigator, click Table Control.
6) Drag a box in the document.
7) If AutoPilots is turned on, select the data source again, but this time select the new
table and then its fields. If AutoPilots is turned off, specify the data source for the form
in the Forms Properties window, then add columns by right-clicking in the top row of
the control > Insert Column > Textbox. Change the properties of the column by right
clicking it > Column...
8) Display the data properties for the sub-form: With the table control selected > click
Form Properties > Data.
9) Change the content type to SQL Command.
10)Enter into content something like:
SELECT * FROM Orders WHERE CustId = :x
Where Orders = the name of the second table, CustId is the name of the field within
that table that has the values in common with the table for the main form, and x is any
variable name.
11)Scroll to the bottom of the list.
12)In Link master fields, type: CustomerId
Where CustomerId is the name of the field in the table of the main form.
13)In Link slave field, type: x
Where x is the variable used in step 11. Do not type a colon this time!
14)Click Design Mode On/ Off and it should be working.

Using Data Sources 5

 Report creation

Report creation
1) File > AutoPilot > Report...
2) Follow the AutoPilot.
3) In the last step (Save Report), choose “Modify report template”.
4) Adjust the column widths. This is a table, so you can drag columns or use Format >
Table... > Columns.
5) Save and close the file.
6) Display the Data source viewer.
7) Navigate to the data source and open Links.
8) Double-click on the report. A new document based on the template will be created.
Note: The report must be closed or nothing will happen.

Using Data Sources 6