Dear all,
I cannot figure out how link in an odb database a table present in another odb database.
Is there a way to do it?
Thanks in advance
Base only allows connection to one database at a time. If you really need data from another .odb you can open both, then copy and paste the table(s) needed from one to the other.
EDIT 09/29/2017:
My answer and comments below have for some time nagged me. Upon further investigation I restate my answer.
The question itself is not entirely clear - link tables in different .odb’s (connected via foreign keys) or joining/accessing tables from different .odb’s.
The answer to either is somewhat lengthy and depends upon terminolgy so just a short version is presented. Since an .odb is not actually a database, what database is being referred to? Sticking with HSQLDB (embedded v1.8 is LO default) there are multiple possibilities.
Embedded - Standalone forms can be used to access multiple tables in multiple .odb’s. Limitations exist as far as what can be done with the foreign data. A big limitation is that LO itself deals with the HSQL opening/access which then limits some of its’ capabilities.
Split HSQLDB - This puts some of the control back to the user. In HSQL you can create multiple schema (an object within the database which appears as a database in embedded) each of which may be dealt with independantly in diferent .odb’s. They don’t even need to be related in any way. With this setup, a table in one schema can be linked to a table in another schema. Tables can be joined across schema. Security can be set as to who has access to what and how it may be accessed. NOTE! This is not a server mode setup.
DB servers are also different. MySQL also deals with schema in the database. PostgreSQL, however, contains databases which can then have different schema in each database. Other servers may be different yet. Referrence to the documentation is absolutely necessary as there are some capabilities to access multiple servers and diferent ways to relate items in each.
So the bottom line here is that it depends upon the exact intention and what database is being used. With the right setup, Base can be used.
Hi
There is only the possibility of linking tables in text format.
See this FAQ on that possibility.
Regards
Thanks for your answers. I know the possibility of linking text files, but my need is to link a table from a different odb database.
From what I see from your answers it is not possible. Is it correct?
Regards
That is correct.
@Ratslinger, Ok, So to join two tables from different databases, are these the only 2 options? A) Copy the needed table(s) from the external (not currently connected) database into your currently connected DB, thereby duplicating your data (really not good) or B) Move all the jointed table(s) into one huge database, forcing all parents (providers) and all children (clients) to be in the same database, thereby destroying modularity? (also not good) Are there any other ideas?
To be clear, the subject is linking and not joining tables.
Theoretically, I can envision an artificial linking through a macro connection to an external DB. This would probably take quite a bit of coding depending upon what is truly needed.
More importantly, it almost appears to be a design flaw when the data you need is located in another DB.
@Ratslinger, Example: shared database of cities, states, zips, country codes, etc, tables. This used by many other, otherwise unrelated, data bases that need address lookup features. Yes, linking would fix it, like in Access where I’m migrating it from, but LO appears missing this ability. For now must make one huge database with all tables in it. What a mess. Conflicting name-spaces … But it will work for now with some extra work. Thanks anyway.
@EasyTrieve your examples are correct. Let me be clear. You can in Base, using macros, connect to other databases allowing retrieval or updating of table information. There no ability in Base to link a table in one DB to a table in another DB.
If you know how to use firebirdsql you could activate experimental features in libreoffice so that you can link tables in different databases. I’m not sure if this is possible or if it works, because i never tried it my self.
Firebirdsql in Libreoffice is on development stage as you can see here. Also you can see this answer for instructions how to activate firebirdsql use in Libreoffice.