Connecting two OpenOffice databases

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
oti
Posts: 1
Joined: Fri Oct 12, 2018 12:06 am

Connecting two OpenOffice databases

Post by oti »

Hi All!

I'm new to OpenOffice. Just decided to try to transport an old MS Access based invoicing system I made to OpenOffice. However, I just got into a challenge after importing the tables.
The thing is, I split the MS Access database into two different database files where the tables were located in one and all the other items in the system was in another file. The reason for this was that I could update and make enhancements to the system without changing the tables at the user site. It was an easy task to develop changes and then just swap the 'system' datafile at the customer site.

I can't find a way to do this with OpenOffice. Any hints?
My need for this separation has become more necessary now as some customers want to access the database from different client computers with different operating systems. My plan is to have the tables database on a windows PC while the system database can run on any client that supports OpenOffice.

On a side note - is it possible to link tables in an OpenOffice database with ODBC or another connection protocol?

Thanks in advance!
OpenOffice 4.1.5 on Windows 10, MacOS X and more
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Connecting two OpenOffice databases

Post by UnklDonald418 »

The first page of the Database wizard used to create a new Base database includes an option to Connect to an existing database. Use the list box to select from a number of connection protocols, ODBC being one of them.
Base is just a front end for a database engine. As distributed with OpenOffice the Base module uses an Embedded database model with version 1.8 of HSQL which is over 10 years old . The .odb file of an Embedded database is actually a zip archive that contains all the database components, tables, queries, forms, reports and also the database driver. It's a handy format but there are some serious issues. Many of those issues can be circumvented using a split database model with a JDBC connection where the tables and database driver are stored outside the .odb file.
I'm not sure if this is what you are needing.
Look here for a discussion of the issues
[Wizard] Create a new 'split' HSQL 2.x database
Create a directory for the database and add the wizard. When the wizard is opened it automatically executes a macro that will create the needed sub-directories and files and add a driver for HSQLDB version 2.3.2
Use Copy and Paste to add the tables, queries, forms and reports from an Embedded database into the split database. The tables will be stored in the database sub-directory, and the queries, forms and reports in the .odb file.
The HSQL driver can be easily updated by replacing the hsqldb.jar file in the driver sub-directory. Be aware that there have been some compatibility issues reported with the versions 2.4.0 and up when using Base as the front end. Legacy versions of HSQL can be found at
https://sourceforge.net/projects/hsqldb/files/hsqldb/
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Connecting two OpenOffice databases

Post by eremmel »

Note that you can still use MS Access database file with Base, but I think that you cannot address two database files at the same time from Base.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply