FrontPage/KexiDBDesign/KexiDBDrivers

MDB (MS Access file) Driver Development

This page documents development of MS Access files KexiDB and KexiMigration drivers.

Table of Contents    1. General Information      1.1. Terminology      1.2. Overview      1.3. Status    2. Progress    3. Creating packages      3.1. Creating a source tarball      3.2. Creating a debian package    4. TODO    5. Development notes      5.1. Discussion: What's wrong with in-place read-write accessing .mdb files?      5.2. Detailed Issues Related to mdbtools      5.3. Exporting and Importing design from/to MS Access files    6. Links

See also: MDBDriver

1. General Information ^ toc

1.1. Terminology

Microsoft Access

database application for 32-bit MS Windows created in early 90's and also offered today. We'll often use shortened term MSA for this.

.mdb file

self-contained Microsoft Access database file - contains data and database schema bundled together.

1.2. Overview

We're using mdbtools to access .mdb files.

See what's new in mdbtools: fresh CHANGELOG from CVS

January 2005: Martin Ellis introduced migration module for .mdb files. Good starting point. Note: THIS IS NOT FULL KEXIDB READ/WRITE DRIVER.

1.3. Status

The KexiMigration driver is at stable stage. KexiDB driver is planned (first, read-only versions will be developed).

You can browse the driver's source code here:

 http://websvn.kde.org/trunk/kdenonbeta/keximdb/

2. Progress

• Install necessary headers from KexiDB.
• Configure test to find KexiDB headers and libraries
• Import of table structures and simple data.
• Separate out current 'mini-glib' implementation.
• Configure tests to allow use of system glib library (glib is required by mdbtools).
• Fix copyright, licence and other boring things
• Import into KDE CVS
• Send Win32 build patch upstream.
• Debian packing files
• Character sets support
  • MS Jet4 encodes characters with utf8, so it works out of the box using ICONV library
  • MS Jet3 encodes characters with ANSI charsets specific to the native operating system's encoding the mdb file has been created on. [1]
• Use string2Identifier to import tables with spaces in their name.
  • (Need to move this into KexiDB from Core first.)

[1] To workaround this problem, MDB Migration driver offers custom option for setting encoding of the database. Then, Import Wizard on importing gets this option from a user (the default is equal to ANSI code for current "system locale"). This allows to import, e.g. database with ANSI 1250 code page (Central Europe) on a "ANSI 1252" (Wester Europe) system.

See also: A discussion on mdbtools mailing list, and http://support.microsoft.com/kb/q202479/

3. Creating packages

Much of the work in it's development involved making it build outside the Kexi source tree, including making KexiDB install development files. This should simplify writing other drivers to be developed outside KexiDB, and result in configure tests that can be reused in other projects that need to use KexiDB.

3.1. Creating a source tarball

You need to have svn2dist installed - it can be found in trunk/KDE/kdesdk/scripts in Subversion, or in the kdesdk-scripts package on Debian and Kubuntu.

 svn co -N svn://anonsvn.kde.org/home/kde/trunk/kdenonbeta/
 cd kdenonbeta
 svn co svn://anonsvn.kde.org/home/kde/branches/KDE/3.5/kde-common/admin
 svn up keximdb
 cd keximdb
 ./build_keximdb_tarball.sh

This will create a tarball in a new directory called keximdb-tmp. To re-run the script, first delete keximdb-tmp. Note that the script uses the KDE's anonymous Subversion server.

3.2. Creating a debian package

As in 3.1 Creating a source tarball, then

 tar -zxf keximdb-0.9.tar.gz
 mv keximdb-0.9 keximdb-0.9.orig
 rm -R keximdb-0.9.orig/debian/
 tar -zxf keximdb-0.9.tar.gz
 cd keximdb-0.9
 debuild -us -uc

4. TODO

(in approximate order)

• Move to koffice-playground
• Configure tests to check KexiDB version number.
• Test import of OLE and other complex data.
• Import relationships.

5. Development notes ^ toc

5.1. Discussion: What's wrong with in-place read-write accessing .mdb files?

 january 2005, js

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

For 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...). Wait a moment, and take a look at following issues:

• Mdbtools provides incomplete write support. Its author already did realy huge reverse-engineering work but, AFAIK, at the time of this writing, adding a new table is at least problematic. For example, people can be fooled on win32 platforms that it's all work well, eg. when recently trying OO.org 2.0beta, but looks like win32's API is used there. Other people reported problems with MS Access files with the same application running on Linux instead of MSWindows.
• Reading tables by simple listing rows is only a small part of the task list. To make a full use of database features user needs to be able to perform database SQL queries with joins and row filters (WHERE clauses), no matter if by entering a query statement or by using a GUI tools. Unfourtunately, to be able to do more sophisticated queries than "SELECT * FROM mytable" (which is in fact equal to a simple table opening), we need to add a Query Engine which can perform joins for us transparently, using foreign keys, and also other tasks like: rows filtering, computing expresions and so on. Such engine doesn't exists within MDBtools and it isn't even planned to be developed rapidly in the near future (or can we hope that Sun will sponsor this effort?).

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 storeage 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.

So what can we do? Proposed solution is to turn our forces to improve migration tools using Kexi migration API and tools.

What else can we do? Read about Cross-DB-Engine Queries, handling queries on higher level - here.

5.2. Detailed Issues Related to mdbtools

• Add 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.

5.3. Exporting and Importing design from/to MS Access files

Note: this tool requires running MSA.

There is undocumented feature in MSA allowing to import and export full definition of a single object to a file. Queries, forms, reports, modules, data acces pages and macros can be exported and imported (i.e. all except tables, but tables can be exchanged using msbtools).

The advantage of this method is that we can see the full schema that is probably very similar in terms of layout to what is stored in memory by MSA. Example fragment of a query design listing inserted tables:

 Begin InputTables
    Name ="Authors"
    Name ="Publishers"
    Name ="Titles"
 End

See also a thread at http://www.dbforums.com/showthread.php?t=378662

The available methods belong to Access.Application object:

 Sub SaveAsText(ObjectType As AcObjectType, ObjectName As String,
                FileName As String)
 Sub LoadFromText(ObjectType As AcObjectType, ObjectName As String,
                FileName As String)

To export a report definition to a file: open the Immediate window (Ctrl+G) and type

 Application.SaveAsText acReport, "ReportName", "C:\reportdef.txt"

You can import the report from file later by typing

 Application.LoadFromText acReport, "ReportName", "C:\reportdef.txt"

acReport constant stands for report type. Other available constants are acFrom, acQuery, acModule, acMacro, acDataAccessPage.

Note 1: These methods are useful to backup MSA databasese objects. Employed in a loop using VBA can export all the objects in one go (except tables, as already mentioned).

Note 2: Funny, but intellisense "knows" about the signature of the methods while you entering the args. In fact you can use Object browser to find these methods and many other: for example select Application class, press right mouse button and set "Show Hidden Members" option on. You will see grayed names of classes and members. Have fun.

6. Links

• MDBTools (WebCVS, List Archive)
• Jackcess - pure Java library for reading from and writing to MS Access databases (originally based on mdbtools)
• Jakarta POI - Java API To Access Microsoft Format Files
• Databasejournal.com: Use System Tables to Manage Objects
• Accessing Query defs in .mdb files without using MS Access (in Russian; english autotranslation) - this info woll be used for implementing MSA query importer.
• GLIB reference
• Description of the new features that are included in Microsoft Jet 4.0
• Using mdbtools with PHP
• MS Jet Database Engine 2.0: A User's Overview at MSA Developer Forum Archives
• Microsoft Access Database using Linux and PHP
• When to Migrate from MSA to MSSQL Server - a source of information about MSA drawbacks compared to db servers
• Compound File Binary Format (CFBF) - on-disk storage format of data, opened by MS for use by others and it is now used in a variety of programs; used by MSA and Business Objects.
• SNP File Format, based on the Compound File Binary Format (CFBF), used by MSA to store Report Snapshots in a single file which can be viewed and printed by the Microsoft Snapshot Viewer (available as a free download from MS). This allows report output to be exported and viewed on computers which do not have MSA installed.