Live link from Calc to Base

English
,
#1

The other day, I found that I could create a live link from Calc to Base, and add a live link from Base to Calc. I was excited, envisioning various ways I could use this ability.

Unfortunately, I found through further research that the Calc-to-Base connection was read-only. This severely limited my field of possibilities, but I still thought I might be able to use joins and other database query features to enhance spreadsheet data handling.

Unfortunately, I found that query joins were not possible. Queries in the read-only database were limited to one table ONLY.

I have been trying to envision a viable advantage (vs using Calc or Base 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?

^^^^^^^^^^^^^^^^^^^^^^^
EDITS:

  1. Apparently it is not clear in my question. I am using Base with Calc as the data source (NOT Firebird, HSQLDB, etc.).

  2. Here is my hardware and software:

COMPUTER
MSI GT780
i7 quad core processor with hyperthreading
16 GB RAM
Linux 4.15.0-117-generic Mint 19 Cinnamon 3.8.9 OS (Installed on SSD)
LibreOffice Version: 6.4.6.2

COMPUTER
Acer Spin 5
i7 quad core processor
8 GB RAM
Linux 4.15.0-20-generic Mint 19 Cinnamon 3.8.9 OS (bootable persistent USB flash drive)
LibreOffice Version: 7.0.0.3

COMPUTER
Acer Spin 5 (same computer as above)
Windows 10 (Installed on M.2)
LibreOffice Version: 6.4.5.2

#2

What Base?

HSQLDB?
Firebird?
Others?

Which operating system?

#3

I edited my original post to correct my inadvertent omission.

#4

What Base?

HSQLDB? Firebird? Others?

#5

If you used Base, only Base, you used Firebird or HSQLDB, someone.

#6

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.).

#7

Yes, but the user say

I am using Base with Calc as the data source (NOT Firebird, HSQLDB, etc.).

#8

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.

#9

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… :slight_smile:

If you send me a first simple example, with data in Calc and Base, I try some techniques.

#10

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.

#11

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.

ref: View (SQL) - Wikipedia

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!”

#12

Thanks for the suggestion, but Views are disabled in the read-only database.

#13

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.

#14

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.

#15

Ok, maybe you could then try to create a database (schema + view) manually and than connect base to it.
Just an idea.

#16

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.

#17

Sorry i could not help you out. Gues i’ll have to up my game with base. :slight_smile: Hope sombody still can provide an sufficinet workaround or solution for you.

#18

Thanks, anyway, for trying to help. Have a great day.

#19

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.

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.