[Solved] How to share tables between databases
[Solved] How to share tables between databases
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
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
Reason: tagged solved
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: How to share tables between databases
Your HSQL external ( for example HSQL Version 2.5.0 ) can have multiple SCHEMAs, for example:
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.
- Schema default: PUBLIC
- Schema: "CRM"
- Schema: "account"
- Schema: "student registration"
- Schema: "family ancestry"
- Schema: INFORMATION_SCHEMA
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.
Re: How to share tables between databases
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
Re: How to share tables between databases
Therefore, your database design is . . . you fill in the blank.gkick wrote:Thank you for the swift response. Unfortunately the AND is no given, the table resides in a separate database.
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.
Re: How to share tables between databases
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
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
Re: How to share tables between databases
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers
---------------------
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers
---------------------
Roses are Red, Violets are Blue
Unexpected '{' on line 32
.Re: How to share tables between databases
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. Sliderule suggests something like this:robleyd wrote:Does http://www.hsqldb.org/doc/2.0/guide/run ... ultiple_db help?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to share tables between databases
And this is how I solved the same problem for me and my small databases.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to share tables between databases
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.
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.
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers
---------------------
Apache OpenOffice 4.1.15
LibreOffice 24.2.3.2; SlackBuild for 24.2.3 by Eric Hameleers
---------------------
Roses are Red, Violets are Blue
Unexpected '{' on line 32
.Re: How to share tables between databases
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.robleyd wrote:I was thinking that, with several databases available, one might be able to use something like
SELECT * FROM CRM_db.TABLENAME ....
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
[Solved]Re: How to share tables between databases
Thank you all gentlemen, interesting topic
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend