[Solved] How to share tables between databases

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] How to share tables between databases

Post by gkick »

Hi all,

Lets say we have a CRM db, an accounting db, a rental db, a student registration db , a family ancestry db,....in other words multiple Base databases and they all need to access data in a table of the CRM db. Since they are all standalone databases, how do you get access to the crm data ?
Is it possible for a db to connect to more then one backend ? Or some sort of end of day replication ? Use Calc as go between ?
Would love to learn how such a requirment can be achieved.
Thks
Last edited by Hagar Delest on Fri Feb 28, 2020 9:09 am, edited 1 time in total.
Reason: tagged solved
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: How to share tables between databases

Post by Sliderule »

Your HSQL external ( for example HSQL Version 2.5.0 ) can have multiple SCHEMAs, for example:
  1. Schema default: PUBLIC
  2. Schema: "CRM"
  3. Schema: "account"
  4. Schema: "student registration"
  5. Schema: "family ancestry"
  6. Schema: INFORMATION_SCHEMA
Therefore, if a particular user has been granted authority to read and or write to a particular SCHEMA or TABLE, AND each SCHEMA is in the same database, all is well.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to share tables between databases

Post by gkick »

Thank you for the swift response. Unfortunately the AND is no given, the table resides in a separate database.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: How to share tables between databases

Post by Sliderule »

gkick wrote:Thank you for the swift response. Unfortunately the AND is no given, the table resides in a separate database.
Therefore, your database design is . . . you fill in the blank.

Perhaps, you could define a HSQL TEXT TABLE, that is, for example, use SqlTool.jar to EXPORT the data from one database, and, you will have that data available ( since the last time you exported it ) from the other database.

HSQL 2.x Online Documentation Text Tables: http://www.hsqldb.org/doc/2.0/guide/tex ... chapt.html

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to share tables between databases

Post by gkick »

Well no, not a question of design as per se, but more of a conceptual thing.

Small , medium size business do not go for full blown CRM, but more likely have some MRP2 module ,some form of contact db, one for invoicing, one for hr and most likely there is a bit of duplication going on.
If I recall correctly, old Access97 had the ability to attach tables from another db and or spreadsheets as well.
O well, can't have it all.
cheers
G
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to share tables between databases

Post by robleyd »

Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to share tables between databases

Post by Villeroy »

I think, this page describes how the same HSQL server runs more than one database. And a Base document connects to one specific database only.
Server with 2 databases sharing one table
Server with 2 databases sharing one table
Sliderule suggests something like this:
Server with 2 databases sharing one table and 2 Base clients
Server with 2 databases sharing one table and 2 Base clients
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to share tables between databases

Post by Villeroy »

And this is how I solved the same problem for me and my small databases. :lol:
Attachments
Shared_Tables3.png
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to share tables between databases

Post by robleyd »

I was thinking that, with several databases available, one might be able to use something like
SELECT * FROM CRM_db.TABLENAME ....

Specifying both the DB name and table name. But if Base is disadvantaged by only being able to connect to one database at a time, guess that isn't a possibility.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to share tables between databases

Post by Villeroy »

robleyd wrote:I was thinking that, with several databases available, one might be able to use something like
SELECT * FROM CRM_db.TABLENAME ....
Yes, if "CRM_db" is the name of a database schema which includes the table named "TABLENAME". This is organized entirely on the server side.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved]Re: How to share tables between databases

Post by gkick »

Thank you all gentlemen, interesting topic
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply