Sharing Base definitions using HSQLDB files (Calc) on multiple computers

ARYLIOA · August 9, 2024, 2:22pm

Can anyone confirm if this approach will work reliably?

I am working to make a transition from Access to Base.

Conditions: (also, please tell me if these contain fallacies):
-We have three (aged) computers running Linux Mint Cinnamon. One used exclusively by me, one by my wife, and one for development, mainly to get into Windows for the things that I just must do there. (All are dual boot but the question applies only to the Linux OS.
-Our primary dynamic data lives on a network drive.
-In almost every case I can just create Calc files for the Access database since I can do without Queries (Filter does the job) or reports. Sharing works fine, of course.
-On several, I really need better means to display the information so I have been working on the best way for our situation.
-The structure of the Base definitions would rarely change. Queries, Reports, etc., once defined would generally be static.
-By definition, I cannot modify the data from Base, but have to do it using Calc. (Page 114 | Base Guide 7.3) That presents no real problem for us.
-Base files connecting to Calc files must be “single user”, I. e. live on the machine using them.
-I understand it is possible to share those Base files using Samba, but that seems more complicated than our situation requires.

I seem to have discovered that I can do “sharing” by creating, on my computer, a Base file with a Calc file on the network drives as the source. Then, if I copy the Base file (.obd) from my machine to an identical structure on the other machines, all have identical function since the definition seems to be intact and the source Calc file on the network drive, is available. The Base functions are as if I were using the definitions on my computer.

That takes a few minutes, but so infrequent that the time seems immaterial compared to the simplicity.

Am I missing something, or setting myself up for trouble someplace along the line?

Villeroy · August 9, 2024, 2:37pm

For simultaneous read/write access to the same database, you need to extract the database from your Base document and put it on a server, where you can run HSQL as a stand-alone program in server mode. What remains on the client machines is the mere Base document with queries, forms and reports but without the database. In this classical database environment, many users can work with the same database at the same time. No, setting up a database server is not a drag&drop operation.

ARYLIOA · August 9, 2024, 3:30pm

And connecting through Samba seems to be a nuisance. Additionally, dealing with IP addresses would likely overwhelm my “What happened now?” wifemate making the thing more work than I care to deal with.

I have 58 years experience with computers, and still enjoy learning new things. So the challenge is almost recreational. But you may deduce from that how it likely won’t be long before it is our heirs who will have to figure it all out. And they are still wed to Windows.

The method I described seems to solve most of our needs in a relatively uncomplicated manner. We are not dealing with large relational databases, but simple records of things like maintenance records for cars and tools, address files, income/expense, and such. For maintenance records, the Calc alone file is just fine. For addresses, a simple form from Base makes the Calc database more readable. For the financial records, I really want reporting for things like medical expenses, taxes, and such . For us, those records are no an “I just crashed my business!” type of thing, but simple personal record keeping.

Villeroy · August 9, 2024, 3:33pm

A properly setup database server is completely transparent. The user opens a form and edits data without saving anything. The server program manages all the technical details.