Sharing Data between Base and a spreadsheet

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Sharing Data between Base and a spreadsheet

Postby Pack88 » Tue Aug 06, 2019 8:12 pm

For many years (30 to be exact) I had an ancient 640k computer that utilized Ashton-Tate DB3. I had with the help of a dummies book written a small program that kept track of my financial affairs and updated it monthly with a simple program that calculated the monthly gain or loss of income in my stock portfolio, IRA's and CD's. It basically worked by copying the files to a temp file updating them and returning them in their updated status to the DB. It was not vey sophisticated but served my needs and as the computer aged it very primitiveness kept it safe from hacking and data breaching.

Well long story short my computer died and after the tech finished laughing when I took it to be repaired, I started trying to figure out how I was going to replace my little financial buddy. I started using excel and in spite of my age taught myself enough to create a spreadsheet that served my needs.

I am however not happy with the way the data looks so I thought why not try Open office and see if I could use the data base function and link it to my excel spreadsheets so I get the nice reports to which I had become accustomed. This has exceeded my capabilities and viewing the many solutions offered on line- none seemed to work either I am dumb as a box of rocks or the methods offered up were for older versions of Open Office.

So after that very long preamble my question is Does Open Office v4.16 (DBase) have a method where a spreadsheet can be linked to a db file so information can be copied updated and returned! Thanks very much for reading this tome
Last edited by RoryOF on Tue Aug 06, 2019 9:15 pm, edited 1 time in total.
Reason: Added spacing to break up "wall of text" [RoryOF, Moderator]
Open Office 4.16 on Windows 10
Pack88
 
Posts: 1
Joined: Tue Aug 06, 2019 7:49 pm

Re: Sharing Data between Base and a spreadsheet

Postby Villeroy » Tue Aug 06, 2019 8:49 pm

File>New>Database...
Connect to existing db
Type: dBase
Specify your dBase directory. dbf is the required name suffix, lower case only unfortunately.
Yes, register the database for exchange with office documents
Save the database.

The built-in dBase driver does not support aggregations nor relations. If you need more dBase functionality, use an ODBC connection through your preferred ODBC driver.

Instead of aggregation queries, you can use Calc's pivot tables with your registered data source.
In order to edit your database table, you can open the data source browser in Calc and in Writer (F4 key). You can also attach input forms to sheets and to Writer documents. Transfer from sheet cells to database records is not possible since sheets have no concept of records nor strict data types.

[Tutorial] Using registered datasources in Calc
Re: Macro to display SQL result in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27098
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 2 guests