Rapid Database Application Development
"Microsoft Access for Linux"
MDB (MS Access file) Driver Development
This page documents development of MS Access files KexiDB and KexiMigration drivers.
See also: MDBDriver
1. General Information ^ toc
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.^ toc
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:^ toc
 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.^ toc
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.^ toc
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.^ toc
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^ toc
(in approximate order)
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:
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.^ toc
5.2. Detailed Issues Related to mdbtools
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.^ toc