If you used Base, only Base, you used Firebird or HSQLDB, someone.
I beg to disagree. Base has HSQLDB and Firebird embedded, but you can use an external datasource instead (such as Calc, Writer, MySQL, MariaDB, PostgreSQL, etc.).
Yes, but the user say
I am using Base with Calc as the data source (NOT Firebird, HSQLDB, etc.).
I AM the user. Apparently we are not communicating. Your quote just made my point, yet your comment indicates you believe otherwise. Thanks anyway for trying to help.
With Macros, maybe is possible, but… data in Calc generally are simple (String, Integer, Long), in DB could be more complex (Decimal, Arrays, etc)…
but, could be funny try it… 
If you send me a first simple example, with data in Calc and Base, I try some techniques.
Thanks, but I seem to have found the answer to my question. It appears there is currently no viable use for this behavior that cannot be better achieved by other means. I am therefore ending my search for now.
I appreciate your offer. Have a great day.
Hello,
if you can only access one table, maybe you can construct a VIEW - table in the database which stores the the JOIN/Query operation an from which you can read what you want. Just an idea. Have not tried it myself.
Hope that helps.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”
Thanks for the suggestion, but Views are disabled in the read-only database.
I dont know much about the Base module, but could you not add the View before making the database read-only ? ← thats a total noob question, because i only worked with “real” databases like mysql, postgres so i dont know what base actaully uses or can do.
No – and I didn’t make the data read-only, Base did.
BTW, Base is NOT a database; it serves as a front end for many databases, including MySQL, MariaDB, PostgreSQL, MS-Access, Firebird, etc. You can also connect to Calc and Writer as datasources, and connect by ODBC, JDBC, and other methods, including streaming.
Ok, maybe you could then try to create a database (schema + view) manually and than connect base to it.
Just an idea.
Already tried setting up a fresh Firebird database but I have found no other way provided to make the connection to Calc or any other datasource except as you create the database. It’s all done via GUI – and the result in the present case is a RO connection. In fact, I read in some of my research that a RO is intended (or at least expected – although I don’t know why). However, my question concerns why queries in this situation can only reference one table, which makes joins to another table impossible.
Sorry i could not help you out. Gues i’ll have to up my game with base. Hope sombody still can provide an sufficinet workaround or solution for you.
Thanks, anyway, for trying to help. Have a great day.
Hello,
First, this is NOT a Live-Link. You can have a live link using macros, but even that should be avoided.
@LKeithJordan wrote:
I have been trying to envision a viable advantage (vs using Calc alone) for the ability to use this live link from Calc to Base, but so far I haven't found any. Does anyone have any ideas?
I am surprised no one had any answer as the one that should be the most obvious is mailing labels. Especially this time of year, every year, there will be an influx of label questions from the once-a-year’ers. Addresses in Calc, create DB, print labels.
Others have used this method to run simple queries on Calc data. It, as noted, is limited.
Edit:
Before going further, I should answer the now deleted (by me when I deleted my answer) comment.
As for the link not being live, Open both Calc and the linked Base file. View the table in Base. Modify the Calc file. Look at the Base table - no modification. Refresh the table - still nothing. Close the table view (not Base), open and look again. Nothing changes. Now close Base, re-open and View the table. Change is present. Not a Live update.
As for joining tables, these are not true database tables. Edit one. There is no primary key.
There is a method to use .txt and .csv files in a read/write manner. See links in this post → Cannot edit imported data in Base
And as for labels from Calc, I am not aware of any method to generate labels from a Calc file without creating a Base file from it first. This does not mean you create it specifically but that the Wizard may do this. Have answered many labels questions and not seen a process which did not involve Base.
Hi Ratslinger. I’m glad you answered. I have in fact created a LIVE LINK from Calc to Base.
As I make changes to the spreadsheet (add rows, add columns, change column order, change tab name), I see the results in Base. In order to create the live link, you must access Calc as a datasource upon opening a blank Base file.
However, you are correct that there is no way to create an index key for these tables. They CAN be accessed by queries, but only one table per query. The tables are also read-only.
My question concerns whether there is a UNIQUE use for this ability. Yes, you can create labels from Base – but you can also use MailMerge to create labels from a Calc file by defining the Calc sheet as a datasource during the MailMerge set up process.
I was hoping for insights as to extra capability, not simply another option to do the same thing. If you would like, I can give you the steps to reproduce the live link. Then maybe you can help me determine a unique purpose.
First, don’t need steps for this. Have created more of these than I care to remember.
Will make a concession here. Upon initial creation, Calc file changes can be made and seen in Base. You cannot see added records if it is more than what was originally entered (unless Base is opened & closed). You can see if a record was deleted. If deleted an added record can be seen in its’ place.
All this goes away after both files are closed (possibly after all LO is closed). Then reverts to what I have stated before - no Live updates. This is using LO v7.0.1.2 on Ubuntu 20.04.1 Mate.
Also, earlier, re-checked MailMerge. Specifying the Calc file there will create a Base file (for me in Documents folder). Was never asked where to put it. Automatically done for me and named using the Calc file name. Can be seen in Registered Base list.
Your comments don’t exactly match my experience in either case. I can think of several possible reasons for our differences, but it no longer matters; I believe the answer to my question is that this particular behavior has little or no significance or noteworthy use that cannot be better attained by other means.
To be honest, I’m disappointed. I had hoped for something to compete with (and maybe even exceed) the QUERY function in Google Sheets. I had even bigger ideas when I first saw this behavior until I began experiencing its limitations.
Anyway, thanks for taking time to answer. I always appreciate your insights concerning the deeper workings of LibreOffice.
Have a great day.
BTW, I’m giving you the check mark.
Have finally discovered method to do Mail Merge from Calc without creating a Base file. This was accomplished in Win 10 with LO v7.1.2.2 (rarely use this OS).
This same process does NOT work in Ubuntu 20.x with same LO version (my normal OS). It DOES create a Base file.