Kexi/Migration/MDB

This is a MDB file migration driver for KEXI.

MDB files are the native database format of Microsoft Access (and also some other MS applications). Access was created in early 90's and is still offered today. We often use shortened term MSA for it.

MDB files are self-contained; they contain both data and database schema.

• Location of the sub-project is in KEXI's src/migration/mdb dir.
• Read the project README.
• You are invited to join the project.
• Contact for support here.

Introduction

The Kexi MDB driver is based upon mdbtools.

Currently the Migration Driver is developed, what means MDB driver can only import MDB databases as a new KEXI database. It cannot be used to edit an MDB database.

Supported features

Currently .mdb and .mde database files are supported because they are the formats supported by mdbtools. They are created with MS Jet Engine version 3.0 (MS Access 95 and 97) and MS Jet Engine 4.0 (MS Access 2000, XP, 2003...). Access 2007's .accdb files are currently unsupported. This format has not been reverse engineered. It also has nothing in common with MS Office Open XML format so there is still no official documentation. Our advice is to avoid this format if possible and stay with older .mdb file format if you have to use Access.

Files created with MS Jet 2.0 (MS Access 2) are not and probably will not be supported. You can use free Microsoft conversion tools (Windows-only) to convert these files to a supported mdb format and import the converted files using the MDB migration driver.

Sample Databases

• Northwind Traders Sample Database
  • See also analysis of this design
• Free MS Access templates from Microsoft useful for test purposes (TODO jstaniek: I have them locally as msa_templates.tar.gz, will upload to github)

Development

Packaging

mdbtools source code is maintained within the KEXI migration scope to ensure that exact version that is required, not too new, not too old, is in use.

Future plans

As soon as mdbtools provide full write support for MDB databases, we wouldd like to extend the MDB support to a full KDb driver. However, it's possible that a read-only KDb Driver could also be developed before.

Maintaining mdbtools within the KEXI migration scope can be stopped if it's possible and practical.

Comparison to Libre/OpenOffice support

mdb-sdbc-driver: oo.org uses .mdb files in read-write mode on Win32 using a native libraries. On Linux, it uses the MDBTools library too. You can also take a look at the patches published on the page. Bqack in 200x years when we monitored the situation, there were just SQL parsers that we wouldn't like to use in KEXI because KDb already had its own, more advanced SQL parser.

TODOs

(in random order)

• Move to KDb?
• Test import of OLE and other complex data
• Import relationships

Read-write support for .mdb files?

(started by JS, January 2005)

By in-place read-write support we mean opening .mdb files without migrating it to other (e.g. more KEXI-compatible) format.

For an average Joe user this topic looks quite simple: we're opening .mdb files, doing changes (adding records to existing tables, adding/removing tables, changing schema, designing and executing queries, and so on...). There are some issues though:

• mdbtools provides incomplete write support. Its author already did really huge reverse-engineering work but, at the time of this writing, adding a new table may by problematic. For example, people on win32 platforms can assume that it's all work well, eg. when trying LO/OO beta, but looks like win32's API is used there, not mdbtools. There are problems reported with MS Access files with the same application running on Linux instead of MS Windows.
• Reading tables by simple listing rows is only a small part of the task. To make a full use of database features user needs to be able to perform SQL queries on the database with JOINs and WHERE clauses, no matter if by entering a query statement or by using a GUI tools. Unfortunately, to be able to perform more sophisticated queries than a SELECT, one needs to add a Query Engine which can perform joins transparently, using foreign keys, and also other means like: rows filtering, computing expressions. Such engine doesn't exists within mdbtools and isn't even planned in the near future.

What about reusing such an engine? It's hard or impossible to effectively take SQL engine out of, say, SQLite project and reuse it just by replacing its data storage layer with .mdb-compatible one. Why? SQLite (and probably most or all engines) are designed and optimized with one particular storage method in mind. Similarity of any such a method to .mdb storage handling seems to be only superficial, e.g. regarding to splitting memory to small pages.

Detailed Issues Related to mdbtools

• No support for MONEY and DECIMAL values. There is a bug in mdbtools (<0.6) related to handling these data types. DECIMAL value is always fetched as "000000."
• Fields ordering. Sometimes order of the imported fields for the given table is not the same as the one visible in MS Access. This can be related to moving and/or removing columns. To fix on mdbtools level.