Use base to connect to shared spreadsheet


I would like to use base to populate a spreadsheet. I remember there is an option in access. Can base do the same: build the database around a calc spreadsheet, but the data is not stored as a copy of that spreadsheet: the spreadsheet is modified by base.

Thank you

This is the opposite of the topic title “Use base to connect to shared spreadsheet” as I understand it. If you connect Base to a spreadsheet, Base will display (but not edit) contained rectangles of cells to be shared with office documents (mail merge, labeles, other spreadsheets).
If you populate a spreadsheet with Base data, you have a Base document connected to anything and pull data from that Base document into a spreadsheet.

[X] Connect to existing…
Type: whatever type of database
[X] Register this database (for use with office documents)
Save the database.
Add some meaningful query.

Calc menu:View>Data sources
Drag the query from the data source window’s left pane into the spreadsheet.
[Tutorial] Using registered datasources in Calc

Thank you, I did not know one could generate a database directly from calc. But the creation is simply through the base routine. Calc simply launches base. And the view>Data sources opens the created database into another window. I am more looking for a separate spreadsheet that is linked to base but that can be accessed by calc for further analysis. The HSQLDB generates a single file; is there another way to have independent files?

  • If you connected to a spreadsheet when “creating” the datasource (I avoid the term database deliberately) your data stays in the original spreadsheet, and can be accessed and altered there. The .odb-File stores only queries, forms, not your data
  • If you created a new “embedded” HSQL or Firebird-Database, then you create an empty database, to be contained in the .odb-file, wich will also hold all data added later.
  • For small solutions with separate data I usually connect to a sqlite-Database through an odbc-driver, but dbase would also be an option (you connect to the directory containing the .dbf-files).
  • You can connect to bigger installed or remote databases like MariaDB, Postgres etc. and you can also use a splitted Version of HSQL with external Data. But remember where your data actually is, when planninng backup or migration…

Another point: You can drag any datasource (not only tables, but also queries) on a new spreadsheet and have a renewable copy of the data there to analyze or to create charts and also have the power of the used database to tailor your query.

Check the documentation for base. I only read the german document, but it shows a lot of possibilities for base.

Base supports JDBC and ODBC drivers, and it comes with some of its own drivers on board. You can connect a Base document to any type of database you have a driver for. Just use your favourit database engine such as MySQL, PostgreSQL, Firebird, SQLite or even MS Access.

A Base connection to a spreadsheet reads rectangles of used cells as if they were database tables. This connection to a pseudo-database is read-only and limited in many ways but good enough for mail merge and label printing.
P.S. It is possible to convert an embedded HSQLDB into a stand-alone HSQLDB.