[Solved] Joining two databases?

Discuss the database features
Post Reply
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

[Solved] Joining two databases?

Post by Editor_HU »

Hi, I've seen the need for Base being able to connect more than one databases and create common views. The last reference I saw was from 2010. Are you aware of any progress please?

I have two remote database with ODBC connection to my machine. I'm keen on viewing joined records from both sources. Is it possible with Libre Office 3.4? Thank you
Last edited by Editor_HU on Fri Jun 08, 2012 12:04 pm, edited 1 time in total.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Joining two databases?

Post by Villeroy »

Connect 2 Base documents to your databases and drag tables from into the other. Should work flawlessly between databases of equal types.

If you want to see SQL JOINs between 2 databases, there is another trick:
Create a form in a stand alone document. Conncect the form to a row set in DB1. Add a subform and connect that one to a row set in DB2 linked to the main form through some common field.
The subform shows records related to the selected main form record.
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
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Thank you very much, I'll come back with my results
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Villeroy wrote:Connect 2 Base documents to your databases and drag tables from into the other. Should work flawlessly between databases of equal types.

If you want to see SQL JOINs between 2 databases, there is another trick:
Create a form in a stand alone document. Conncect the form to a row set in DB1. Add a subform and connect that one to a row set in DB2 linked to the main form through some common field.
The subform shows records related to the selected main form record.
Used two Excel workbooks as odbc source. Created a Base file for each. Opened the two Base documents in table view, and tried to drag a table from one Base doc to the other. It was not possible to drop a table to the other Base window.

Subforms: unfortunately I'm not familiar with subforms. Would be glad to learn, but I failed to find a Base users guide. Could you please give me a link to a usable Base user manual?
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Joining two databases?

Post by Villeroy »

Spreadsheets are no databases. Pseudo-databases based on spreadsheets are read-only.
Spreadsheets have sheets with atomic cells. There are no tables, rows nor fields in a spreadsheet. A database driver for spreadsheets interpretes the used cell ranges of a sheet as if they were a database table. It does not convert nor import anything into a database.
What you tried to do is just the same as opening the documents copy sheets from one document to the other one. A database program will not do that. This is what you have to do in a spreadsheet program.
The other solution with input forms might be rather insufficient since there are no indices in spreadsheets.

You may keep the sheets and find a solution with VLOOKUP or something like that.
You may import spreadsheet ranges into a true relational database which can present data from many interrelated tables as one.
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
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Villeroy wrote:Spreadsheets are no databases.
Thank you, this info falls in the range of my skills
Villeroy wrote:Pseudo-databases based on spreadsheets are read-only.
Spreadsheets have sheets with atomic cells. There are no tables, rows nor fields in a spreadsheet. A database driver for spreadsheets interpretes the used cell ranges of a sheet as if they were a database table. It does not convert nor import anything into a database.
What you tried to do is just the same as opening the documents copy sheets from one document to the other one. A database program will not do that. This is what you have to do in a spreadsheet program.
No, my problem is not a spreadsheet issue. I have a real database and in addition important but irregular data are stored in spreadsheets. I need a mailmerge from the database but I need to include those not-in-the-database-yet data too. Yes, the ultimate solution is putting it all into the database, but I need those data on the prints before they manage to redesign the whole application and add new tables, new fields plus user interface.

Using two spreadsheets was just a test case to check if I can do what you suggested: drop a table from one base to an other to let them join together. Base could handle the join when the source tables were in the same database, but I could not make the cross-database connection you suggested. Does this have anything to do with the fact that the two base-es refer to excel-based odbc sources rather than for example two Oracle references?
Villeroy wrote:The other solution with input forms might be rather insufficient since there are no indices in spreadsheets.
I didn't get this clear. Is it really problem that spreadsheet does not have index? Once the spreadsheet became an odbc source and Base could query against it's joined tables, how can the nature of the data source become an issue when the join would happen between two databases rather than inside the Base? What makes the difference between the two cases?
Villeroy wrote:You may keep the sheets and find a solution with VLOOKUP or something like that.
You may import spreadsheet ranges into a true relational database which can present data from many interrelated tables as one.
No, the direction is not the spreadsheet, rather the database. Importing is the right thing, the point is I need 'join two sources' the solution until somebody makes the time- and money consuming integration. I need the merged prints before that :-)
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Joining two databases?

Post by Villeroy »

Editor_HU wrote:No, my problem is not a spreadsheet issue. I have a real database and in addition important but irregular data are stored in spreadsheets.
The Excel data are not in the database. They remain in the spreadsheet. It takes a spreadsheet program to modify either the structure (move sheets) or the data (sheet contents).
The same Base connection can not bundle links to one or more databases with data from sheets and other sources. MS Access can do that but it won't let you edit the sheets neither.
The form/subform solution requires that you are familiar with manual creation of forms (we have something in the tutorial section).
For similar tasks I use to dump everything into one spreadsheet, rearrange things to properly formed tables and use that scratch pad as a merged data source.
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
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

May be it's a language problem that I don't understand you.
Villeroy wrote:It takes a spreadsheet program to modify either the structure (move sheets) or the data (sheet contents).
I'm not speaking about modifying the spreadsheet's data from the Base side. Jost read and combine with data from other sources.
Villeroy wrote:The same Base connection can not bundle links to one or more databases with data from sheets and other sources.
When I connect to an Oracle database, I do it using ODBC. When I read a table defined in Excel, I do it using ODBC. ODBC should hide the nature of the orogonal data, from Base point of view I want to work with two ODBC sources and am keen on combining fields from them.
Villeroy wrote:MS Access can do that but it won't let you edit the sheets neither.
Never wanted to edit the table contents from Base. Only purpose of Base is to support the mailmerge.
Villeroy wrote:The form/subform solution requires that you are familiar with manual creation of forms (we have something in the tutorial section).
I created forms in several other systems. Used queries and subqueries. Still I don't get the point how could I use the data which I managed to bring together in a subform, to supply combined records to OpenOffice mail merge.
Villeroy wrote:For similar tasks I use to dump everything into one spreadsheet, rearrange things to properly formed tables and use that scratch pad as a merged data source.
As long as you are alone you can do this. Once there are more people filling the sources and more people willing to mail merge, you may not count on your personal expertise and proper copy-paste practice. I was hoping some help from OpenOffice Base, but momentarily it seems it isn't OO to handle the whole issue. I did not get any closer to take two records from two independent sources, combine them based on a key field, and use fields from the combined record in Writer. Either I'm dumb or Base is not the proper tool for this.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Joining two databases?

Post by Villeroy »

When I connect to an Oracle database, I do it using ODBC. When I read a table defined in Excel, I do it using ODBC. ODBC should hide the nature of the orogonal data, from Base point of view I want to work with two ODBC sources and am keen on combining fields from them.
Well, Base can not do this.
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
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Thank you for the help, it's a sorry end.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

An even more sorry end, a simple, single-source mailmerge permanently crashes the application. That may be the end of the open story, a year wasted on a volatile limited tool.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Refreshed my LibreOffice to 3.5, tried the thing again. The Writer shows the document properly. F4 data source can be selected, the query runs, the rows display properly. When I go print, select form letter print, merge to single document, the process starts, then crashes the application. Seems like my work is lost, there seems to be no way to recover. Very, very, very bitter experience.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Joining two databases?

Post by Villeroy »

This is not normal. [Tutorial] The User Profile
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
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Villeroy wrote:This is not normal. [Tutorial] The User Profile
Dear Villeroy, I appreciate your efforts to respond my posts, but I'm very sorry, I don't understand your words nor your intentions. I know every words in your resoponse, but don't know what are you talking about. Would you like me to leave the forum?
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Joining two databases?

Post by thomasjk »

Villeroy means that the program crashing is not normal behavior. Follow the link in his post to reset the user profile which may correct your problem.
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Joining two databases?

Post by Editor_HU »

Followed the user profile cleanup tutorial, the system still crashes when I try to print. I did not take the atomic method, used the ultimate neutronic-atomic-thermonutlear-intergalactic way: went to a virgin Linux / Libre Office machine from my Win7 one. Left out the Excel thing, it is now clearly LibreOffice, one single database, one single documents with field referred to the calc table. Trying to print (merge), Ubuntu experienced an internal error.

Open / Libre Office does not want me. And I do hate it too. Invested so much efforts in migrating from MS, fought with colleagues to accept it, and now when we should harvest, the whole thing collapsed, the work will not be done and the step back to MS Office will be full of pain and shame.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: [Solved] Joining two databases?

Post by Editor_HU »

Finally I wrote the documents manually, the document continued to crash the application when I tried to merge. Tried the user profile cleanup, went to a freshly installed, clean computer, the system crashed there too. Finally I made tests with all new database and main document, and it worked. Someting must have happened to either the odt or the odb that allowed to work with both the letter and the database but crashed it all when it came to actual merging. So the side issue is worked around.

The answer to the original question wether it is possible to mail merge using Libre Office Base so that Base pulls the fields together from local records with reference to the central database and the central database, the unfortunate answer is NO. Thank you for all your patience and help.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Post Reply