Description: 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.
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
Adding some database experts, what do you think?
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.
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.
Many thanks for submitting the enhancement idea, Art. But in this case we rather abstain from adding it to the application.