For other topics, same author, go to AUTHOR'S MAIN SITE

"FDB" pages: "Free Data Base"...

LibreOffice / OpenOffice Database Tutorials

Custom Search- Results will open in new window or tab

I have notes for translators, if you would add a translation. This "translations" block grew so large, that only recent translations appear here. Others are available further down the page. Sorry! But you can jump to the right place... it is on a sister-page to the one you are reading.

.

And also into...Bosnian, Czech, Estonian, Finnish, German, Indonesian, Macedonian, Portuguese, Slovenian and Ukrainian.. (This link will take you to a sister-page to the one you are reading. (You may be able to access translations in German, Serbo-Croatian and Hungarian, too.) And if none of those suit, there's...

"ooBase", aka "Base"

LibreOffice/ Open Office's free, multi-platform database

(Also has articles about other LibreOffice/ OpenOffice components)

Page contents © TK Boyd, Sheepdog Software, 2/06-4/20.




LibreOffice? Open Office? If I were starting fresh today, I would "go for" LibreOffice. This link will take you to a sidebar on the similarities and differences, the issues.

Whether you are using OpenOffice or LibreOffice, I think you are wise, and hope that my tutorials will as useful to you as to those using the other product.




Overview...

...Sheepdog Guides Tutorials about using databases to Make Life Better.

(I also offer a short quick "executive summary" of why I recommend that you bother to learn about LibreOffice, for the database module in particular.)

   The first three sections show you how to start. They save you time by showing you the basics you will need, if you are going to make progress easily on almost any aspect of database work. (It is very inefficient to learn to use databases the way, say, you learned to use a wordprocessor. One random bit at a time.)

The sections marked with an asterisk are part of a separate page, which the link will take you to. The summary below is repeated there, so it will be easy to get back here, if you want to visit one of the sections here later.

   Supporting Materials

Main Open Office Base project manager window

   Once you're past the stuff in those introductory tutorials, you should be ready to jump around to whatever has your interest in the following.

   And then there are these odds and ends...



I try to make my pages browser friendly. Re-size your browser window so it is as wide as you want it. The text will flow nicely. It is easier to read in a narrow window... and the rest of your screen will be free for your ooBase exercises! Hold down the control key ("ctrl") and press the plus sign, the minus sign, or zero to change the size of the text in Firefox and other good browsers. This and another good hint explained on my Power Browsing page!


Please use Facebook Likes, etc

There are buttons at top of almost every page, unless you are using Firefox's Facebook container. Or mention these pages in forum discussions?

If no one reads these things, I don't have much reason to update them, or to write more. (Is that your message to me?) And no one reads them if they don't hear about them. Google searches cater for the masses. And the masses use a smartphone and have a 15 second attention span. These pages aren't for that audience.




Jump to page's Table of Contents




The globe on the right helps me see if doing these pages is worth the time it takes. It is interactive; play with it, if you wish. (You need to use your browser's "back" button to get back here after doing so.) The only information the system records is when visitors access the site, and where their ISP connected them to the internet. (In my case, that is 60 miles from where I live, at the moment.)

This comes to me as a free service from RevolverMaps.com. If you know how to insert HTML into your own web pages, it is easily added.

(Please do not include the code for the Revolver Map in translations of this page.)




Macros:

I won't promise that nothing involving a macro will appear anywhere else, but I will try to keep most things involving macros here.

I sweated blood to become familiar with doing macros, and overcoming some (old) limitations "at the (previous) frontier" of ooBase. About two weeks after I'd finished, I became aware of a new version of ooBase. Limited tests suggest that it overcomes the limitations ! !    This is an "Hurrah!", not a moan. (Well, a bit of a moan... for the fact that I timed my adventure badly.)

With ooBase 3.1 you can store macros within a given database's .odb file. You can store macros in the database they are for. You will also be able to have images in reports. (Give upgrading serious consideration, if you are still using something from before 3.1. I've done several OpenOffice upgrades over the years, under Windows, without regrets.. but take the usual precautions.) (For images in reports, you also need the Oracle Report Builder, previously known as the Sun Report Builder, or SRB. I did my tests with version 1.1. So far I can get a form to display images held in the database and images which only have the path to them stored in the database. So far, I can only get reports to print out images of the former sort. Another Good Thing in OO3.1 + SRB 1.1 is that a "Keep Ratio" option has been introduced for displaying images.

= = = = =

Macro programmers: start with these...

OOP and Events: A an important tutorial covering fundamental elements and principles involved in any macro work with Open Office module. Some of the material here will help you understand practices that I follow when working with macros which are not "laid down" by the general requirements of Open Office. Not an easy read, but I commend it to you.

First steps with Macros: An important tutorial if you want to work with macros in any Open Office module, or understand material in any of my other macros tutorials. A simple "Hello World" button is created to illustrate sundry points. Many things explained in words of one syllable. A beginner's tutorial, or a review for intermediate macro programmers.

Miscellaneous thoughts on macro use: Not very well written, but if you want to master using macros, working your way through this will give you additional knowledge. Among other things, in this tutorial we look at working with a checkbox on an ooWriter document. Again: Explained in words of one syllable.

Macro programming: the second level...

Running some buttons with ooBasic: In this tutorial, you are given less help, you have to have some grasp of what you are doing... which you can attain by working through the tutorials above, if need be.

User-defined functions: Creating new "words" for Open Office work, especially ooCalc. In this tutorial, we create a function which works out the "with tax" price of something when we know the "before tax" price and tax rate. Once we've done the things explained in the tutorial, we are able to merely put things like =tkbWithTax(200,3) in a worksheet, and get 206.

Working with files: A macro which will delete one file, and copy another. Note: This "work" is on closed documents sitting on your disc, not with "stuff" inside a file. (One of the files can be open at the time the macro is called, but we only copy the current disc copy of that. Even unsaved changes to the file are not copied.)

Document properties: This looks at... without finding all of the answers... examining the properties of the document your macro is stored in. Along the way, we look at some of the documentation available to Open Office programmers.. the API and ooBase.

Macro programming: the third level...

Accessing functions and procedures inside external DLLs. A Windows .DLL is provided for your use while taking your first steps. An example is developed using an ooCalc worksheet, but I believe the principles are general. A function in the DLL is called; it returns a string, which we then display in a cell of the worksheet. A subroutine in the DLL puts "Hello World" on the screen in a message box, when something happens in the ooCalc worksheet to invoke the subroutine.

Macros with ooBase...

The following tutorials assume that you have some knowledge of working with macros, which you can gain by working through the tutorials above!

Exporting CSV with the click of a button: This is not just for people who want to export CSV. If you want to use macros with ooBase, read this essay. It takes you through the basic skills, using exporting a file to CSV as an example. It starts almost from scratch, but if you having used macros in Open Office before, or are a beginner, you would do well to review at least the first two tutorials in this, the "Macros" section. They talk about "simple" macros in Open Office. Everything they entail will be needed for working with macros in ooBase... and more!

Where are the macros? This short essay discusses where macros are stored, and has details about a related ooBase issue.


Jump to page's Table of Contents



Please humor me? (Unrelated to Open Office... there's more on that further down!)

Take a moment to visit my http://Flat-Earth-Academy.com? It aims to help kids (or adults who missed out in school!) interested in science know what the questions are, to attain a "basic framework" of knowledge, on which they can build a good understanding. No "sugar", not a log of flashy graphics or page layout... just some concentrated Good For You stuff. There must be SOME people left who aren't afraid to "work" their way to the expansion of their knowledge? Also mention it in any relevant forum you visit, please? (Opens in new tab or window, so you can get back here just by closing it.))

Visit http://Flat-Earth-Academy.com... please!

(Opens in new tab or window)



Discourses:

The following pages are less "keystroke- by- keystroke", less in the "how- to" vein. They are more like lectures. They are probably not as easy or as fun to read, but do give them a try? The are listed from "easy/ important" to "less easy / less critical" topics. While the one about data types isn't particularly easy, it is particularly important! Of course, I can't be sure which are important to your needs. I hope they are all useful!

= = = = =

Cross populating forms with data from more than one table: If your database has more than one table, it is a Very Bad Idea to enter any data twice, i.e. "duplicated" (You hope! That's one of the problems) in two places. This incomplete essay talks a bit about this, and about how you can put data from more than one table into a single form, to bring together the things you want.

Relationships and Joins: As soon as your databases have more than one table... which shouldn't take long... you really, really ought to start setting "relationships" in the database. And before long, you will need "joins". They are not the same thing, but the differences constantly blur in my mind, and I hope to help you fight that problem.

Data "types": Critical to table design. (There's a quick introduction to data types in the "First section", too.)

CSV data files: A short, general explanation of "Comma Separated Values" files, aka "Comma Delimited" files... useful for moving data between applications. The specifics of using them in Open Office (generally) are explained elsewhere... specifics of importing data, of exporting it, and of moving data between Open Office applications.

Run SQL command directly? Analyze SQL?: ooBase is (for a database!) novice-friendly. However, there comes a time when its "help" gets in the way. Then you run your SQL directly. Who cares. When. How to do it. (This page is merely about using SQL commands within Base. I have written elsewhere about using MySQL servers.

Apache / MySQL / PHP: Ready for a challenge? (A huge challenge.) Want to use ooBase with a free multi-user database? (ooBase on its own isn't multi-user.) Want to access databases across a LAN or across the web, again using ooBase at the client machine? Want users with varied and narrowly defined privileges? This essay tries to help... but it isn't a quick read! (Along the way, it also talks about the fun you could have creating dynamic web pages by means of PHP.)

"Just" a MySQL database server, with Open Office as "client", or "front end": This would be a challenge too... though not quite the challenge of the previous adventure. MySQL is a free multi-user, multi-platform database server. you can access a MySQL server across the internet. (ooBase on its own isn't multi-user or amenable to remote access.) The set of pages explaining a great deal about getting a MySQL server going were created in February and March 2013, and go farther than some of my earlier efforts with the topic. Master what is in them, and you will have made a good start towards being ready for the BIG challenge of embarking upon an Apache/ MySQL/ PHP installation, the item I mentioned before this one.



Jump to page's Table of Contents



Case Studies:

Examples of databases filling specific needs. Also case studies of elements of database design. Don't look at the subjects of these case studies and say "Telephone numbers list? I don't need to do a telephone list."

They all illustrate general points, and all may give you "That's a good idea!" thoughts.

If it felicitously happens that you do need to set up a telephone numbers list, well and good, However, I attempted to achieve a more general usefulness as I wrote the case studies. In each, you read about decisions I took in solving a particular problem. You read about tools that I've used. You should take away ideas which are useful in many databases... for instance the database you are trying to create today!

= = = = =

Primary Key design: Tricks of the trade. A discussion of the primary key I designed for the "Books" table of a database I actually use. (It is about a collection of books.) Many generally applicable points.

Family History: A database of ancestors: A work in progress, but significant elements of a database to "pour" what you know... and how you know it... about people from the past. Marriages, children, important dates, etc.

Linked Lists, DNA, Family History: This essay is a little different from most in the site. It talks (at length!) about a problem, and makes some progress with analyzing how to solve it. But a full, Open Office Base solution is not yet available. I hope someone can send a solution! Until then, the essay remains a valuable exercise in looking at data, and how it might be coded. The data would fill a matrix but sparsely. The data might best be stored in a linked list. Can such a structure be held in an RDBMS? The whole thing arose along the way to designing a database for matching DNA for the purposes of family history research.

Department of Motor Vehicles- in Paradise: This database is a foundation for other tutorials. The database can be downloaded. It has four tables, with relationships established. It is the basis of a data management system for a hypothetical Caribbean island's traffic police.

Form with two sub-forms: A modest little thing. In the context of a small database with information on plants as an example, a form with two sub-forms is created.

Annual giving to charity: A database for keeping track of donations to worthy causes. No "rocket science"; a fairly basic system.

Stock Market Investing: Notes on a database which tracks essential data for a stock market investor. Also, I hope, of interest to novice private investors, for general points on records you need to keep.

A "real world" example: Description of some rather "messy" work done to satisfy some real world needs.

Project Participation: A database tracking the people working on a group of projects. Useful as an illustration of having "lines" of info for multiple entities, and other things. You can download the database, if you wish. Not "complete" yet... but the description of the tables, useful in its own right, is.



Jump to page's Table of Contents


Other resources... Alternate (second choice) set

These are my "second choice" alternate resources. I have my top recommendations elsewhere. That link will, as is usual on my pages, open in a new tab. Just close that to get back here.

If you are new to databases, please realize that, powerful as they are, you will not be able to drive one as easily as you did your first driving of wordprocessors or spreadsheets. But databases ARE worth the trouble you will have to take.

If you were an apprentice carpenter, you might find yourself having to learn that a hammer doesn't make a very good screwdriver. Hammers work... but there are better ways.

It might pay you to study some general pages about relational database management software. (That's what most of what we call "databases" are... even if we don't always use the "relational" part... at first!).


-----------------
There are other Open Office tutorials out there that I would commend to you.

For ooBase, try the following...

Of course, you should read OpenOffice's own Getting Started With ooBase (PDF document)... although, sadly that may well be dated... the page was still headed "Getting Started with OpenOffice.org 2.x" when I checked it in May 2020, and Base has been in version 4 for a long time, since well before 2015, and there are big differences between Base 2.x and Base 4.x. But! Either the Open Office people are asleep at the wheel, or they think it still gives good guidance on general principles.A big part of my not-easy decision, /20, to move to Libre Office is based on the seeming dwindling participation in the Open Office effort by the people who built it.

Forgive an untidiness here? This next should be in a different section.

In the "official" Open Office site's wiki "department", there is a lot of good documentation. Proper manuals, etc.

The further back you go, in terms of the Open Office version, the better the support. For version 2, there were online versions, pdf, and you could buy a "print on demand" ink-on-paper tome.

Visit Wiki.openoffice.org/wiki/Documentation/, and have a little "wander" around the various "Guides" sub-sections.

For general OpenOffice tutorials, try the following...

The following will give you links to a bunch of good OpenOffice material, much more than ooBase...Open Office's own Documentation Project articles. (That page is about the API and the programming language built into Open Office, and active across all apps... for macros, etc, but it has links to other pages. It may be slow to load. At 5/2020, it was still there, but I haven't used it for years, can't tell you how up to date it is... or isn't.) And the material is available in many languages. Personally, I find the material hard to navigate, perhaps suffering from "committee-itis"... but their is Good Stuff there, if you dig. Maybe you won't even feel as frustrated as I was; my aversion probably just a matter of "style". But I should also add that it comes from the same stable as the badly dated "Getting Started..." mentioned above, as does the manual on the programming language I mention next...

The ooBasic Wiki

Please...

Please don't ask for a link from here to your site unless a) your site already has a link to this site, and b) the Wayback Machine will show me that your site has been up for more than two years.

Were the above not what you wanted? Please consider looking at my top choices for Other Resources, which are on the page that will open a new tab for.




Editorial Philosophy

I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.

I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.

Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also...

Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. (See page about doing translations.)


Jump to this page's Table of Contents





If you liked what you found here, or want different things, here are some more pages from the editor of these tutorials...

The homepage of Sheepdogsoftware.co.uk., my biggest site. (Apologies if the "?FrmFdb1Main" I added to that link causes your browser problems. Please let me know, if so? It is there to help me learn my visitors' preferences.)

Using the Arduino Microprocessor: These things are Just Too Much Fun, if you have even a little bit of Geek. You don't need a lot of money. Check out the introduction, anyway?

Pages about using computers in Sensing and Control, e.g. weather logging.

Programming: Lazarus... a Pascal based free, open source, multi-platform language. And it works! Tutorials.

Programming: Delphi and Pascal tutorials.

Introduction MicroLan: a powerful family of devices for sensing and control. This is not consumer-level stuff, but neither is it not beyond the means of enthusiastic amateurs. I also have tutorials for you about programming 1-Wire (tm) chips and devices for MicroLans (tm).

Another of my sites. (Arunet)


Search the ooBase tutorials. Results will open in new page. (You will have to use other searches to check all of my online material. Or my "index" pages, e.g. the one for SheepdogSoftware.co.uk.)

Custom Search


Jump to page's Table of Contents





Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However this work doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages.


To email this page's editor, Tom Boyd... Editor's email address. Suggestions welcomed! Please cite the page that gave rise to your question, and say if you use OpenOffice or LibreOffice. Your OS (Windows? Linux? Mac?) might also be making a difference.)



Valid HTML 4.01 Transitional Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.

AND passes... Valid CSS!


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . . . P a g e . . . E n d s . . . . .