Compatibility with microsoft access97

[paul22]

I am seeking an Open Source alternative to MS Access, at least ‘as good as’ Access97 - as I have a number of Apps I developed around 1995 - '97, that I want to convert to an Open Source alternative, with as little modification to the Access design as possible.

According to Wikipedia there is very little choice on the Windows platform. I read article ‘Open office dilemma: OpenOffice.org vs. LibreOffice’ of Feb 2011, but that has little on ease of migrating Access Apps to ‘Open’ or ‘Libre’. It stated “expected Microsoft Office compatibility to be a major area of competition between the two suites, but alas, not much work seems to have been done there”

About 8 years ago I tried ‘importing’ 1 of my Access 2.0 Apps into OpenOffice.org (think Ver2) but it was a waste of time as only the underlying tables imported ok - the simplest bit; the complex part: Forms, Reports, Queries and other glue tying them to the Tables, failed to import.

My Forms made extensive use of ListBoxes, ComboBoxes, RadioButtons wherever it helped ease the UI - a central part of design. Also used SQL & Macros, and a feature of Access that autoprinted a helpful table-link schema showing all Fields & Relations. I need the best alternative RDBMS offering closely equivalent functions to minimise the work in converting the older Access 2 & 97 Apps.

In my initial search for documentation like a ‘Reference Manual’ for ‘Base’ in order to compare its specification with Access, I couldn’t find much at libreoffice.org only a very basic “Getting Started with Base” …was then directed to Open Office ! Further links took me to hsqldb.org where I did find what looks a dated but useful “Base tutorial From Newbie to Advocate” In all that, I got impression the bigger documents were writen for the ‘Open not Libre Office’ version of ‘Base’.
Is BASE identical in Open & Libre suites ?

Ideally, I would like a link to a ‘Reference Manual for Libre-Base’, a ‘Designers Guide to Migrating Access to Libre-Base’ - that shows what Functions in early (access97) have no direct equivalent in Base, and suggested workaround.

One Function I would like to know its limits, is max range of real ‘Date’, and if Julian Date is supported. - a Reference Manual should have that info.

If needed, can BASE be run standalone without need to load or even install the spreadsheet & WP ?

Any help in understanding what features of Access97 are missing from Base, and maybe suggested workarounds will be much appreciated

I thank you for your time and help in advance.

[iplaw67]

Is BASE identical in Open & Libre
suites ?

Essentially, yes. Main difference :
LibreOffice now includes an embedded Firebird engine, which is not yet the default, but can be made visible in the UI options (db creation wizard, etc) found under the experimental options of the general configuration. WARNING - firebird integration still has numerous issues, hence the experimental tag.

Ideally, I would like a link to a
‘Reference Manual for Libre-Base’, a
‘Designers Guide to Migrating Access
to Libre-Base’ - that shows what
Functions in early (access97) have no
direct equivalent in Base, and
suggested workaround.

There is no such Designer’s Guide documentation. There is a LibreOffice 4.0 Base Handbook, which contains an appendix with some technical data in it, although I doubt as complete as you would like. It has not yet been updated in English to the current production version of LibreOffice, however, a version in German does exist. You can find a paper copy on Lulu.com or a free PDF for download here

Forms, Macros, SQL queries designed in Access are still not automatically available in LibreOffice (or Apache OpenOffice). In LibreOffice, there is an integrated set of macros called Access2Base that are designed to assist people in moving their Access dbs to LibreOffice.

[oweng]

“Apache OpenOffice uses hsqldb 2 as the default embedded db engine”. Are you certain about this Alex? I just had a look through the AOO v4.1.0 install files and the core03 deb contains a hsqldb.jar indicating v1.8.0.11 in /META-INF/MANIFEST.MF. A can’t see anything in the AOO release notes mentioning a change to HSQLDB v2.x. My answer here is related.

[iplaw67]

Owen, having just checked against 4.1 on OSX myself, you are correct (#HSQL Database Engine 1.8.0.10). The code to move to hsqldb 2 was submitted though, but I guess in the end the project decided not to integrate it ? I don’t know the reasons why, it was certainly planned.

[peterR1]

Base can act as a front end to many databases, so getting the data out of access and into base is no real problem (if I remember rightly it is possible to use the data directly from access in windows but not in Linux). Access can also act as a front end to other databases.
There is no way, so far as I know, of directly converting forms, queries etc, from the access front end to the Base front end.
I have no knowledge of the differences, if any, between LO and OOo base but hsqldb is but one database usable with Base.
If you want an all rolled into one file with the forms, tables, queries etc in it, stick with Access. If you want a multiuser, server based database use something like mysql or postgresql and consider base as one possible front end but expect to build everything, bar the data, from scratch.

[paul22]

Thankyou for your reply. As I said, when making an initial attempt to convert 1 Access97 App into OpenOffice about 8 years ago, the tables & data imported - that was the easy part.

So my enquiries are directed at converting the meat of the App: i.e the Forms, Reports, Queries, other glue, to ‘Base’

My environment is Windows7 single user laptop, no need for multiuser, remote servers or other OS.

On that, you simply state start from scratch. Surely the Forms and SQL in Base - be it ‘Open’ or ‘Libre’, and the Access versions must have many functions in common (eg Listboxes) ? ‘Reports’ are largely constructed from SQL - so surely it should be possible to import all the SQL statements into ‘Base’ to reduce the rework?

As it happens, I no longer have easy access to the Access2/97 disks I originally bought, just 1 reason, why I am considering an open source alternative -dont want to pay twice for the same thing.

So most of the questions in my initial post remain unanswered. Since that post I did a little more googling on the subject, but majority of hits refer to the pre-Libre ‘Open’ version of Base; one old post around 2007 concluding ‘Base’ was a much inferior product than Access - but without giving reasons. Another ‘old’ post said Base does not have the ‘Switchboard’ type Form that makes for a very useful toplevel heirarchical Menuing system in Access.

• Does anyone know if that functionality has now been added to Base ?

• At the very least, there should be some central repository of useful documentation including the ‘Reference Manual’ I asked about before, from which in the absence of anything else, I can hopefully compare the ‘Differences’ between ‘Base’ and Access.

• Again, can someone familar with the Date function in Base, point me to a reference to its date limits, and if there are to/from Julian Date conversions ?

• Can Base be fired up standalone without the overhead of the WP and Spreadsheet running also ?

• On the subject of hsqldb, I only mentioned that because in my hunt for a ‘Base’ Reference Manual, I came accross dated but useful “Base tutorial From Newbie to Advocate”. I later saw that in the new ver 3.5 release note, it seems to be recommending one uses ‘postgres’. Is there a similarly useful equivalent to dated but useful “Base tutorial From Newbie to Advocate” written for the new Libre version of Base ?

[qubit1]

Hi @paul22,

Thanks for posting your question to this site. It sounds like you have a lot of different questions, and it would be really helpful if you could post each of them separately so that we could answer each in turn.

Each of the following sounds like it could be a useful question to post:

• “a ‘Reference Manual’ for ‘Base’, in order to compare its specification with Access,”
• “Is BASE identical in Open & Libre suites ?”
• Is there “a ‘Reference Manual for Libre-Base’, a ‘Designers Guide to Migrating Access to Libre-Base’…what Functions in access97…have no direct equivalent in Base, and suggested workaround.”
• Information about “limits, is max range of real ‘Date’, and if Julian Date is supported”
• “can BASE be run standalone without need to load or even install the spreadsheet & WP ?”

We’ll do our best to answer each question, but it might take us a little time to get around to all the new questions posted each day. To help speed up the process, we’d love for you to join us in answering questions for other people!