Bug 158736 - database feature to catch up to MS office 97
Summary: database feature to catch up to MS office 97
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.0 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Keywords: needsUXEval
Depends on:
Blocks: Base-Enhancements
  Show dependency treegraph
Reported: 2023-12-16 20:17 UTC by art
Modified: 2023-12-19 07:49 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description art 2023-12-16 20:17:53 UTC
A feature in MS Access that would be very useful in Base is the ability to easily write back to the database after using spreadsheet features (numerical analysis) on the data. MS Access all the way back to 97, would let you paste-link spreadsheets as tables in the database. Not just import a copy of the spreadsheet as a table, a linked data source, meaning every time you open the linked table, it has the latest data that is in the spreadsheet.

Then the GUI for queries, that included UPDATE and INSERT queries (letting you drag and drop fields to write to), would let you write the numbers from the spreadsheet into the database. You would run the UPDATE from the linked spreadsheet to the fields in the database tables.

Currently this may be possible with SQL commands and code, but it would be difficult for most users. Without the GUI assist, commands typed in are very error prone. Few people will be comfortable writing command line type commands to a database. Most users of an office package are not database administrators.

The database is the best way to store data. The spreadsheet is the best way to work with numbers. You need an easy way to get the improved numbers back into the database. Make the two programs work together, not just easy one way import from Base to Calc.

The use case is numerical models that do not fit into the 2D sheet, or even the 3D sheets. Big models that need a database.

Actual Results:
None, not easily possible - hence new feature.

Expected Results:
Start with data in Base, import to Calc, use functions to improve the data (maybe optimise), then write back or append data back into Base.

Reproducible: Always

User Profile Reset: No

Additional Info:
MS Access had a lot of features easily run from drop down dialog boxes. It made automating data manipulation easier just like the function interface in the spreadsheets makes entering the syntax of correctly formatted functions easier. You could run a long sequence of commands one after the other. You could link a button on a form to do it all with one press.
Comment 1 V Stuart Foote 2023-12-17 01:26:35 UTC
Support for .ods and external spreadsheet formats is read-only for BASE, don't see much need to do more as SQL remains the viable DBMS query and Wizards can't do more than the SQL based tools for loading/copying table data to Calc sheets to manipulate.

No valid reason to expend the dev effort, IMHO => WF
Comment 2 Heiko Tietze 2023-12-18 10:31:36 UTC
Adding some database experts, what do you think?
Comment 3 Robert Großkopf 2023-12-18 15:21:35 UTC
At this moment you could create a database connection to a table document (Calc) and another database connection to any other database. Then it works to copy data from Calc to the other database by macro. I'm using this for importing external data into an internal database like Firebird or HSQLDB.

It isn't running directly by GUI of Base. One Base file - one connection.
Might be it is like bug 137893.
Comment 4 Gerhard Weydt 2023-12-18 16:18:38 UTC
I can't see why it should be better to store those data in a daabase instead of a spreadsheet. There isn't given a convincing reason for that, the statement "The database is the best way to store data." is too general and hence of no value. What is wrong with having the data in the spreadsheet?
I don't understand the use case: if the numerical models do not fit into a sheet, how could a sheet be used to make calculations with these data?
Anyway, the use case is very special. On the other hand, the requested feature would very probably a big task, given that a spreadsheet can very easily be altered (adding rows, columns, cells) which would always have to be reflected in the database.
I am of opinion that the request is too specialized for a general solution. I also doubt whether a spreadsheet is the best way to work with, but then the use case isn't quite clear.
Comment 5 Heiko Tietze 2023-12-19 07:49:43 UTC
Many thanks for submitting the enhancement idea, Art. But in this case we rather abstain from adding it to the application.