MEPIS Community Forum

I maintain a number of databases for voluntary organisations which I need to transfer from Lotus Approach to an application which is more widely used. They are relatively small (up to about 3000 records) and have 3 to 5 tables each. The main databases are a membership application, and a concert booking application with invoicing.

LibreOffice Base would be an obvious choice for availability and portability, but I'm finding difficulty in getting started. I suspect I would have the same problem with any alternative. Most of the documentation and tutorials I can find spend a lot of time on basic design and writing queries, where I don't have much difficulty, and don't give much detail of complex linking (one table to 2 instances of another) or presentation of printed outputs.

I would welcome any suggestions for the application or pointers to better documentation.

Phil

I use MS Access mainly because it's compatible with the data bases my clients use, and it supports VisualBasic. It's also extremely easy to set up queries in Access by "connecting the dots" though the graphical interface. If you don't require those features, then Libre Office Base may be an acceptable, free alternative. However, I must admit that I haven't used Libre Office Base to know if it's a 1:1 replacement for Access, and I haven't found a comprehensive tutorial for L.O.B. to compare it with Access. My guess is that L.O.B. lacks some of the important features that Access provides.

However, using a full-blown data base program may be like using an atomic bomb to kill a gnat. If you don't really need a relational database to generate queries that link multiple tables, and if you don't need sophisticated report-generating capabilities, then maybe a simple spread sheet program may be all that's required.

Thanks, joany. I've never used MS Office applications - the cost of a version including Access was always more than I was prepared to spend. And getting away from Approach will almost let me cease Windows use altogether, so It's not really an option. I do need a relational database for both main applications, and I need one which makes it easy to print 1200 forms from the primary table.

I looked at a spreadsheet solution for the concerts application, but navigation through and sorting of a 70-sheet spreadsheet was just too cumbersome. I didn't even try to solve the printing problems. I think it has to be LO Base or similar.

Phil

Have you thought about just MySQL with a GUI frontend of some kind? If on a Linux platform, there are a number of frontends available through the repos. For example, mysql-navigator:Other possibilities in this post and the Comments: http://bit.ly/9RZIAI

@Jerry - I had wondered about MySQL, and I'll have a closer look at that. I would have a personal tutor to sort any problem - a colleague designed and produced our website using SQL and html - http://coursemeasurement.org.uk

Phil

If your databases are small enough (specially for the length of character fields, limited to 254 bytes) dBF can be a choice. My wife uses a database with about 30 field and > 3000 records on LibreOffice Base, based upon this dBF (xbase) structure.

The main point is: which are the export formats of Lotus Approach (csv, xbase, etc)?

If your requisites -now or in the future- are bigger, MySQL can be a good choice. If you need small footprint, try Firebird (RDBMS).

Kexi is a good and easy front-end for MySQL and other databases. Knoda was even more flexible, but seems that it is not longer maintained.

You can find both LO Base and Kexi on the MEPIS repos.

Kexi is nice, IF you are going to be on a Linux platform. If you are (staying?) on a Windows platform, you would have to install KDE for Windows to use it, I believe.

Thanks, both - there's some interesting suggestions there, and I now have some reading and testing to do. It should keep me quiet for a bit.

I'm leaving Approach mainly because I'm one of the few that still use it, and the organisations are a lot younger than I am and likely to survive me. I need to leave something that others could use. If I no longer used Approach, I could almost get away from Windows - I would just keep a VBox installation of XP for my 2 GPS programs.

Approach can use and export a number of formats, but the current data is in dBase IV .dbf format. To illustrate the 2 databases I'm most interested in, here are screenshots of the relationship views.

This has 200 records in the Names table and about 300 in Order11. The 3 *11 tables will be replaced for next year.


This has 2800 records in the Person table (growing weekly) and 1100 in the U3A 2011 table. There are only 2 tables - Person1 and Person2 are 2 instances of the same table. The number of fields in these tables represents less than half the total used - the rest are calculated fields which Approach calculates on the fly. For transfer to another app, I can export the values of the calculated fields.


Field size is not a problem - none of the fields are over 100 characters.

I'll spend a while looking at the alternatives, and maybe come back with more questions.

Phil

A couple of months later and I'm no further forward on this. It may be my preconceptions of what a database application should do - my only previous database work has been with Alpha 4 in DOS and Lotus Approach in Windows. Both these applications made it easy to handle calculated fields and design forms and reports, and I've failed to find anything equivalent.

Summarising the main requirements, I need a single standalone multi-platform relational database application to handle relatively small numbers of records. I would prefer to do the development work on Mepis, but the likely users would probably be using Win or Mac.

LibreOffice Base appears to fit the bill, but I can't find any help or tutorial on anything other than the basics. As far as I can see, there is no function to 'alias' a table so that it can be linked twice to another. Also, I can't find anything equivalent to a 'calculated field'. A simple example of the sort of use I need is creating a field 'John Smith' from fields 'John' and 'Smith'. The next stage is to combine 2 names to give the correct version of ' John & Anne Smith' or 'John Smith & Anne Brown'. The resulting fields are used in a number of forms and reports. Is there a way of doing this?

I also find designing a good-looking input form painfully slow. I seem to have to adjust the size of field boxes one at a time, when the same adjustment is needed for 6 fields. Is there any way around this?

As an example of what I'm after, this form took about half an hour to complete. It contains a sub-form with a number of calculated fields. It comes from the first application in my previous post.

Does anyone know whether what I want is achievable? Sorry to sound so plaintive, but I've spent many fruitless hours on this.

Phil

The only guide I have used with some success is this one:

http://sheepdogguides.com/fdb/fdb1main.htm

The OOo Wiki is useless for Base. On the more complicated stuff: when in doubt, I try to do it the Access way and see what happens!