@@ -2,25 +2,28 @@    !!!MDB (MS Access file) Driver Development   -@@@ +This page documents development of MS Access files KexiDB and KexiMigration drivers.   -See also:MDBDriver +@@@   +See also: MDBDriver    !!1. General Information -!1.1 Terminology +!1.1. Terminology  ;__[Microsoft Access|http://en.wikipedia.org/wiki/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 +!1.2. Overview  We're using [mdbtools|http://mdbtools.sourceforge.net/] to access .mdb files.    '''See what's new in mdbtools: [fresh CHANGELOG|http://cvs.sourceforge.net/viewcvs.py/mdbtools/mdbtools/ChangeLog?view=markup] from CVS'''    January 2005: __Martin Ellis introduced [migration|KexiDataMigrationAndSharing] module for .mdb files.__ Good starting point. Note: __THIS IS NOT FULL KEXIDB READ/WRITE DRIVER__.   -!1.3 Status +!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/

@@ -48,7 +51,7 @@    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 +!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.

@@ -61,8 +64,8 @@    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 3.2 Creating a source tarball, then +!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/

@@ -94,6 +97,42 @@  !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|http://mdbtools.sourceforge.net/] ([WebCVS|http://cvs.sourceforge.net/viewcvs.py/mdbtools/], [List Archive|http://sourceforge.net/mailarchive/forum.php?forum_id=5183])

@@ -107,3 +146,5 @@  * [MS Jet Database Engine 2.0: A User's Overview|http://www.microsoft.com/accessdev/articles/jetwp.htm#JET] at [MSA Developer Forum Archives|http://www.microsoft.com/accessdev/a-archive.htm]  * [Microsoft Access Database using Linux and PHP|http://bryanmills.net:8086/archives/2003/11/microsoft-access-database-using-linux-and-php/]  * [When to Migrate from MSA to MSSQL Server|http://www.microsoft.com/sql/solutions/ssm/access/whenmigrate.mspx] - a source of information about MSA drawbacks compared to db servers +* [Compound File Binary Format (CFBF)|http://en.wikipedia.org/wiki/Compound_File_Binary_Format] - 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|http://en.wikipedia.org/wiki/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.