Caveat in using LibreOffice Base to access MS Access Database

Microsoft's MS Office (Office XP) hostility to being deployed in a virtual machine has once again driven me to desert MS Office to use LibreOffice. It is not that it cannot be deployed in a VM but when one relocates the VM from one machine to another, MS Office's activation scheme rears its ugly head. Rather than re-activating, which only solves the problem temporarily, I have decided to ditch MS Office.

In so doing I have discovered a rather disturbing bug when trying to access MS Access (ver 2002) database from LibreOffice Base (ver 3.4.5).

In LibreOffice Base (Windows version 3.4.5), you can connect to MS Access directly using LibreOffice's driver or you can connect to MS Access via the MS Access ODBC driver.

The former connection technique seems to have some bug in handling the MS Access database and the latter technique using ODBC driver should be recommended, at least for this version. While the direct connection technique can open the database and access the tables and queries (they are all I have so far tested) producing seemly the right results. But on closer examination, it reveals some disturbing results.

The direct connection technique always reports one record less than what is the actual total number of records in MS Access. LibreOffice Access connector always does not show the first record of the result set. The missing record is always in the record set from the execution of the query. For example if the first column is your sequence number from 1 to whatever in increasing number.

If you sort that column in ascending order, record corresponding to sequence id 1 is not shown and the total number of records reported on the bottom of the view shows one less than what it should be. The record with the highest sequence number is the last record.

If you sort that column in descending order, the record with the highest number of sequence number is missing as it should now be the first record in the grid but now you will see the record corresponding to sequence number 1 as the last record. The total number of records is the same - one less than the actual number.

It does not matter whether you are querying a table directly or executing an Access Query, it always drops the first record of the result set in the grid. It seems the connector has an out-by-one bug or perhaps there are two index bases internally - one base zero and the other base 1 - and the program or driver fails to provide the correct mapping.

Now if you reconnect from Base to the very same database using ODBC connectivity by defining a ODBC Database source using MS Access ODBC driver. The above mentioned idiosyncrasy does not appear.

In conclusion: Do not use LibreOffice Base's MS Access connection driver; use ODBC connection technique to avoid missing record.