AUTHOR'S MAIN SITE  »  OVERVIEW: client/ server work  »  Intro: MySQL  »  Setting up MySQL
Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon

Using Open Office's database ("Base") as front end for access to MySQL database

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is fully explained, and there are more tips, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software, 3/13.

Connecting a user across the LAN

This essay is somewhat skeletal at present... but I hope it will never- the- less guide and/ or comfort you.

Before you can use Base as your front end for multi-user access to a MySQL server, you need quite a few things in place. How to get them there is the subject of other tutorials from me.

Before you attempt multi-user access, or remote access... they do work, by the way... get a MySQL server and an Open Office "Base" client, or front end, co-existing happily in a single PC.

The next stage is to get at least one separate PC on the same LAN as the original MySQL server talking nicely to the server.

Once all that is in place, you can go for multi-user or, alternatively, remote access, (or both!) to the MySQL server.

Getting either of those set up may give you a few headaches... But it would give you a sophisticated system, with things you might find useful, but impossible in a simpler system.

Until that much is working, there is not point in starting on the challenges of extending it to a multi-user or remote access scenario. You really will be making life difficult for yourself if you try to "run" before you are "walking" smoothly.

I've written elsewhere in general terms about MySQL, and client/ server computing. You can do it!

How to connect one user to MySQL server across LAN

To do that, first you need to know the local IP address of both machines. (I use the non-standard acronym LIPA to emphasize that I am talking about a local IP address, as opposed to one "from the internet". (I call the latter WIPAs!))For the simple "will it work?" test I wanted to do, it was okay to take a chance that the LIPAs wouldn't change during the time I was conducting my experiments. If you want to set up a server for regular across-the-LAN or across-the-WAN (i.e. internet) use, you should take them off your DCHP server, and given them static LIPAs.

I had to set up a user on the MySQL server authorized to access the database from the client's LIPA. Don't forget that the user definition is a several.. at least 3... part process, with parts easy to miss out. (I used MySQLWorkbench to do this. Parts: Server Access Management and Schema Privileges... two parts to that: Which schema/ what rights)

Once those things are in place and you know the LIPA of the PC with the MySQL server, you complete the Database Wizard as before, filling in "server" in step 3 with the LIPA of the PC the server is on, instead of the (or 'localhost') used when server and client were both on the same PC.

By the way, you can set things up to use one "user" to access the server multiple ways. Eventually, you will even be able to have that "user" concurrently accessing the server from multiple clients!

Now... one caution... and this applies equally to the scenario we will address in a moment, having users on the database who are connecting from outside the LAN, over the internet...

The system isn't "magic".

(The problems I am about to discuss do not, I'm glad to say, make all multi-user access to a single database unwise. But you do have to consider all that is going on, all hat is possible. If your multiple users only need read access to the data, then that alone make the "problems" (below) go away. And, with care, you can even have multiple users with more than read-only access. And the system of access privileges is incredibly fine grained. And there may even be record-locking mechanisms I haven't discovered yet!)

Suppose user "A", in Tulsa, and user "B" in Timbuktu, are BOTH in the midst of adding a new record... and BOTH of them are adding a record with a primary key of, say, "27"?

It won't work. You cannot have two records in a table with the same value (27, in our example) in a primary key field.

What will happen when Tulsa and Timbuktu try to add two records with "27"?

It will depend who first tries to post the new record. Whoever tries to post first will succeed. When the other user tries to post the second record with 27 in the primary key field, the result will be a message saying "Error inserting new record. Duplicate entry '27' for key..."

Unless the database user who tried to post second is pretty clued up, this will be confusing. There will be no record with 27 in the primary key field...on that user's screen at the time. If, however, the user understands all that is going on, and guesses that someone else just entered a new record with that value, then the user will probably click the "refresh" button, decline the offer to save the changes to the current record, and try again with a different value in the primary key field.

An even more dangerous situation arises if two users are attempting to edit the same record, without making any change to the value in the primary key.

Suppose Tulsa wants to change the "Amnt" field to 44 in the record with primary key "27", and Timbuktu wants to make it 55.

They can both do that! Or it will seem to both of them that they have done it. However... one of them will post his/ her change first. And when, a moment later, the other posts his/ her change, that will (almost) immediately wipe out the change of the first- user- to- post.... and the first user won't even know! (Unless the first user hits the "refresh" button after the second user has "changed the change")

These "problems" are not insuperable. The "answer" will depend on the specifics of the database work you need to do.

Now... where were we?...

I expect you came to this page from one of two places. You can probably just close the window or tab that this is in, and find the page you came from underneath, scrolled to where you were before. But if you are worried, here are links to those pages, a little something extra to jump you down to where I send people off to this page....

Link to page about multiple users on a MySQL server.

Link to page about accessing MySQL server across internet, i.e across "WAN".


Have you heard of Flattr? Great new idea to make it easy for you to send small thank you$ to people who provide Good Stuff on the web. If you want to send $$erious thank yous, there are better ways, but for a small "tip" here and there, Flattr ticks a lot of boxes which no one else has found a way to do yet. Please at least check out my introduction to Flattr, if you haven't heard of it? "No obligation", as they say!

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..

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 Sheepdog Software/ Sheepdog Guides material for inmate education. Situations do exist where good internet connections are not possible!)

Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this 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!

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

If you liked this tutorial, see my main webpage for more things from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.

Valid HTML 4.01 Transitional Page WILL BE tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.

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

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