Forum at OOoForum.orgThe Forum
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Set up concurrent multi-user database access!

Post new topic   Reply to topic Forum Index -> Base
View previous topic :: View next topic  
Author Message
General User
General User

Joined: 29 Mar 2012
Posts: 8

PostPosted: Tue Jun 12, 2012 5:11 am    Post subject: [Solved] Set up concurrent multi-user database access! Reply with quote

Dear all,

Subject: Set up concurrent multi-user database access to work across the office network!

After a couple months of tinkering and reading many, many posts, I finally got my database to work across the network with multi-user access and decided to write this post as a small contribution back to the OpenOffice/ LibreOffice community:

Note: My special thanks to DACM, as his posts have been invaluable to me, but also to all others that have contributed.

One of the key posts is as follows... but there are many, many others and from many people

* * * * *

As you can read from the various forums, the default Base runs off an embedded HSQL database, with single user access only. Furthermore there are very substantial stability issues (the database can fatally crash with no chance for recovery). For serious database work, the current set-up with the embedded HSQL should be avoided.

My solution: (And it might not be the best, but this one works for me. Also please note that there might be many areas of improvement, as I arrived here mostly by knocking my head on the table and trial and error).

Install H2 database on your computer (The purpose being to run H2 as the database engine back end holding the data, with LibreOffice Base as the front end holding the forms, reports, queries, etc.).

Once H2 is installed (for Windows) execute cmd.exe.

Type the following code (after navigating via "cd c:\..." to the H2\bin folder):
java -cp h2*.jar -tcpAllowOthers

If you read the very good H2 documentation you will see that "-tcpAllowOthers" enables/ allows for multi-user access.

Note: If you want to keep the h2 database data file in the network then try:
pushd \\XX.XXX.X.XXX\XXX\XXX\ etc 'H2\bin folder in network directory, the
java -cp h2*.jar -tcpAllowOthers

Please make note of your IP address. If you've successfully started the H2 database engine your computer's IP address will be shown in the browser's address.

If the H2 database is working then proceed:

Open LibreOffice, create a new database. Any sample blank database will do. Then go to Tools, Options, Java. Select Class Path, Add Archive, and then select the "h2-1.3.167.jar" file in the H2/ bin folder.

Close LibreOffice and restart.

Then create a new database, select "Connect to an existing database" and then JDBC.

If the database resides in the local network use:
h2:tcp://XX.XXXX.XX.XXX//folder/folder/etc/database file name

Otherwise if local (e.g. hard drive)
h2:tcp://localhost//folder/folder/etc/database file name

Username use default "sa" (system administrator).

Then for the driver "org.h2.Driver".

All this is also described at least briefly in the H2 documentation files.

* * * * *


Note: Your office IP address might not be fixed (meaning that next morning when you boot your machine you might have a different IP address). This is fine with H2. But your Base settings are fixed so the IP address will not match. Hence the solution is to "fix" your IP address. If you have admin rights this is very easy (in Windows simply check "Internet Protocol Version 4" and select "Use the following IP address" as opposed to "Obtain an IP address automatically").

* * * * *

Another note - by the way: if running the database in an office environment with multiple users - you need to be aware that whomever starts the H2 database engine (e.g. the database focal point/ admin), then other users will be running off that persons computer... meaning to say that if the admin closes the H2 database then other users will not be able to access the database.

* * * * * update

I have found some problems with the new setup (H2 back end database as opposed to the HSQL embedded database). Subform linking does not appear to work (when selecting the appropriate options in the link master fields/ link slave fields).

Solution: Edit menu, go to database > advanced settings > special settings and check the "replace named parameters with '?' " box.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> Base All times are GMT - 8 Hours
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group