How to create a database using OpenOffice Database

– otherwise known as Base



I am using OpenOffice 4.0.0

I found it rather difficult to get started in OpenOffice DataBase otherwise known as Base, and the help available from the internet was not making things easy for me. I was looking for a beginners guide, suitable for me as a newbie giving me the first steps in data base creation. Eventually I found some sites that showed me how to create pieces of information, and by combining these I managed to progress to understanding terminology and in the creation of my database.

I am an hands-on person and all I wanted to do was to open Base and begin building straight-away. The drop down help from Base/Help/OpenOffice Help F1 was not much help, - too much technical information but not enough information for the absolute beginner or newbie like me. I wanted to start creating my database, not to become proficient, proficiency can come later when I understand and need more.

Thus, having got past the first stage I thought I would write up my findings and offer my insight and help to those who like me just want to get started.


I needed to create a simple database of the 150 club members of which I am secretary. Fairly straight forward. I can learn and modify etc. as I go along or at a later stage. I just needed to create the database in order that I could start controlling the membership.

Now your needs of a database might be different from mine, but what I am going to explain in the following paragraphs will get you started in understanding enough to build your own database. In any case a database of club membership or of friends and family for Christmas Card exchange (or suchlike) is probably one of the more common uses for databases.

A brief description of what a database is.

Consider a Visiting Card or a Business Card (they are one and the same but I will use Visiting Card), - they contain basic pieces of information which may be, for example

                                                    Name

                                                    Address

                                                    Landline Number

                                                    Mobile Number

                                                    Description

                                                    Other information

Now, you might have 50 or 100 visiting cards and some might be of plumbers, others of electricians, or builders etc. How do you sort out all the builders from the rest without going through the whole collection. This is where a database comes into its own. You can ask it to sort out all the builders in a particular area, or all those who have a particular first name for example. And the results of this sorting will be only those that you are searching for. You probably get the drift on this.

Thus you can only get out information that you put into the database.

The terminology used in OpenOffice is slightly different from some other databases like Microsoft so this might be useful information.

The database when it is created and opened is normally viewed as a Table, which looks almost identical to a spreadsheet and consists of rows and columns. Each row contains all the information in a record or Visiting Card. The columns contain information that is common to all rows.

Putting information into a Database is normally done via a Form (this is like the equivalent of a blank visiting card described above), although it can be done in the Table once the Table has been designed and created.

To speedily get you on your way to creating your database we will start by opening OpenOffice and selecting File/New/Database and you will be presented with a window - fig 1

fig 1

ensure that radio button 'Create a new database' is selected and click Next.

In the next window - fig 2

fig 2

ensure that radio button 'Yes, register the database for me' is selected, and click Finish. The word Register in this instance is a bit of a misnomer because what it really means is that you are allowing the database to inter-react with the other OpenOffice functions such as Writer and Spreadsheet. You are NOT allowing registration with any other programs or websites.


Click on Finish and you will automatically have a screen asking you to save your database creation. For this exercise I will Save it as 'club.odb'. This will then cause another window to open thus - fig 3

fig 3
Now with this screen you are given the choice of Tables, Queries, Forms and Reports in the left hand Database pane, and in the Tasks pane a choice of 3 options.

We will now select 'Use Wizard to Create Table', which takes us to - fig 4

fig 4

Now, we need to select certain fields to create our club database, and the ones we discussed earlier are :-
                                                    Name

                                                    Address

                                                    Landline Number

                                                    Mobile Number

                                                    Description

                                                    Other information

We need to expand on this however

                                                    Title (Mr, Mrs etc)

                                                    First Name

                                                    Last Name

                                                    AddressLine1

                                                    AddressLine2

                                                    AddressLine3

                                                    AddressLine4

                                                    Landline Number

                                                    Mobile Number

                                                    Notes

Thus we need 10 data fields

We need now to do a bit of massaging to get 10 data fields to our liking so, click the 'Radio' button on 'Personal' and go to 'Sample tables', click the down arrow and select 'Addresses'. We now need to select 10 of these fields to satisfy our requirement and thus I have moved across to the right hand pane (by clicking on the single arrow) the following :-

                                                    Title

                                                    Forename

                                                    Surname

                                                    Address

                                                    AddressID

                                                    PostTown

                                                    Postcode

                                                    PhoneNumber

                                                    MobileNumber

                                                    Notes

Note that these field labels do not have spaces.

By using the up and down arrows on the right hand side you can sort then into the correct order.

These field labels do not have the labels that we would like but they can be edited very shortly.

If you do this your window should look like fig 4 above. Click 'Next' and this takes us to 'Set field types and formats'; we will do nothing to this window so click 'Next', and in the next window 'Set primary key' we agree to have a primary key added, so click 'Next'.

The next window asks what we would like to call our table and I will name it 'Club Table', and also I want to modify it, so I need to activate the modification with the radio button. Click 'Finish' and you will be taken strait to the design window - fig 5

fig 5

Here we can change the field labels to our liking. You will notice that wizard has added a field at the beginning – this being the primary field that we agreed to earlier. I will speak more about this later.

For now however, we need to change the field labels to suit our requirements. - fig 6 below shows the result.

fig 6

Hit 'Save' icon,and close the window, and you will revert to OpenOffice Base window which will now be showing our Club Table in the lower right pane - fig 7

fig 7

By double clicking on the highlighted 'Club Table' you will open up our table and it should now look like - fig 8

fig 8

This then is your empty database, waiting to have information to be put into it.

Now for the Form; the easier way to enter data into your table.

Go back to your OpenOffice Base window and click on 'Forms' in the left hand pane, and click on 'Use wizard to create form'. The window that appears will have all the data fields that we created and modified earlier, residing in the left pane. - fig 9

fig 9

By clicking the double arrow in the centre we can transfer all of these to the right hand box, and because we have already sorted them into the correct order, we do not need to use the up and down arrows to the right. Modify if you wish.

Clicking the 'Next' button takes us to a request for a sub form. We do not need a sub form so click 'Next'.

This now takes you to the 'Arrangement' of your form. Now you can choose whatever you like, but I will select the left most of the 4 options, and I suggest you do the same as it will be easier and clearer later in the instruction. You can always modify later. The lower set of 4 options are greyed out since we didn't select sub forms earlier. So my selection is - fig 10

fig 10
selecting 'Next' takes you to 'Select entry mode' – just leave this as is, ie the radio button selecting 'the form is to display all data'. Click 'Next'.

The next window asks you to choose the style of your form, I'm staying with the default style as seen in - fig 11

fig 11

and also keeping the radio button on 3-D look. Click 'Next'.

- fig 12 - The next, and last window in this wizard brings you to 'Set the name for your form'. I will rename the form as 'Club Form' thus keeping akin to Club Table which we did earlier.

fig 12
Hopefully, the wizard will create the form as we like it, but if not we now have the option in this window to modify the creation. So by selecting the 'Radio' button to modify the form and clicking 'Finish' we are taken to what the wizard thinks is its layout of the input form, the data of which we input into the grey boxes which will finish up in the database. - fig 13

fig 13

This layout is good, bot not quite to our liking. We can improve this.

Areas to modify will be :-

                                        reducing the size of 'Title' box

                                        resizing the 'AddressLine2'

                                        correcting the loss of 'MobileNumber' label

                                        and generally making the form visually better.

This fig 13 is now in 'Design' mode and we can start amending things.

You will notice that if you click on any of the grey data input boxes it will be surrounded by 8 small green boxes that encompasses both the data box and its label, and if you drag this box around the page you move both the data box and the label. This is because these 2 items are 'Grouped'.

You can 'Ungroup' by right clicking on the grey data input box, and scrolling down to 'Group' and clicking 'Ungroup'.

In 'Ungrouped' mode you can click and drag either Label or Data Field boxes and align them together so that the Label clearly indicates and refers/relates to the Data Field box. You can also resize the the boxes this way. When done, select the 'Pointer' arrow at the top of the left hand side, and click and drag a rectangle around these 2 items. Don't worry about the blue box disappearing when you take your finger off the mouse, it will still be there, but hidden. Immediately right click within this hidden rectangle and in the box which appears go to 'Group' and select 'Group' to the right. If you now click on the text box you will find that you can now move the white text box around the page and the text label stays with it. You can at any time click on the white text box and select 'Group'/Ungroup' to split these 2 items and group them again when you see fit.

Also while in 'Ungrouped' mode you can, by right clicking on the label or data box, have the opportunity to modify the properties of that particular label or data box. Experiment.

You have probably now got a reasonable version of an input form.

I decided to give my form some personal identity by typing a heading 'My Club Database'; just type directly onto the orange background. My version is shown as - fig 14

fig 14

Hit the save icon at the top.

You may wish to have the 'ID' box the 'Primary Key' box, and also making it auto increment. You do this by:-

  1. Open the OpenOffice Base file – the one shown in fig 3
  2. Select 'Tools/SQL'
  3. In the 'Command to execute' box, copy and paste the following SQL Code:-

    ALTER TABLE "Club Table" ALTER COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY;

    Ensure that whatever you type between the quotation marks is identical to your listing in the OpenOffice Base window – it's case sensitive.

  4. Press the 'Execute' button
  5. You should see a message, 'Command successfully executed' . . . assuming the table and field name was entered correctly in (c) above.
  6. Press the 'Close' button
  7. From the Menu go to View and then Refresh Tables
  8. Now, if your Edit your table, from the Tables button on the left, and, right click on your table name, and, select Edit from the drop-down menu, for the field in question, at the bottom, you should see: Autovalue of YES.
  9. Now, you will no longer enter a value for the field, - the database will 'manage' it for you automatically.

You have now got a working database, albeit for club use.

The preferred way to input data into the database Table is to use the Form.

By opening OpenOffice Base and selecting Forms in the left hand pane, and right clicking on Club Form in the lower right hand pane, you will have the option to 'Edit' or 'Open' the form. Ensure that if you edit - you save your edits. By going straight to 'Open' you can begin adding data to your table.

By going through the sequence described you can now create your own database either by starting afresh or by modifying the one you have just created.

I wish you success.





HTML Comment Box is loading comments...