A common problem I have had is to migrate data contained in an access database to other databases or formats.

I have used the tclodbc extension [http://www.solagem.fi/~rnurmi/tclodbc.html] to generate an XML file descibing the Access data and this file can then processed with XSLT to recreate the database on another platform.

The following tcl script demonstrates how the XML file is generated:

  ####################
  #
  # Packages
  #
  ####################
  package require tclodbc

##
#
# Procedures
#
##

#
# Certain characters must be encoded in XML
#
proc Encode {str} {

   #
   # Standard encodings
   #
   set enc(&)  {&}
   set enc(<)  {&lt;}
   set enc(>)  {&gt;}
   set enc(\") {&#34;}

   #
   # HACK ALERT
   #
   # Single quotes cause problems in SQL INSERT statements
   #
   set enc(') {&#39;&#39;}

   return [string map [array get enc] $str]
}

#
# Take an Access file and dump its contents to an XML file
#
proc XMLfromAccess {dbFile xmlFile} {

   #
   # Assume that we are converting Access files
   #
   set driver "Microsoft Access Driver (*.mdb)"

   #
   # Connect to db
   #
   database db "DRIVER=$driver;DBQ=$dbFile" 

   #
   # Start XML file
   #
   lappend xml "<?xml version=\"1.0\"?>"
   lappend xml "<ODBC file=\"$dbFile\">"

   #
   # Obtain the Schema details
   #
   lappend xml "\t<SCHEMA>"

   foreach tableDef [db tables] {

      set table(TABLE_QUALIFIER) [lindex $tableDef 0] 
      set table(TABLE_OWNER)     [lindex $tableDef 1]
      set table(TABLE_NAME)      [lindex $tableDef 2]
      set table(TABLE_TYPE)      [lindex $tableDef 3]
      set table(REMARKS)         [lindex $tableDef 4]

      #
      # Only process normal tables
      #
      if {[string compare $table(TABLE_TYPE) TABLE]} {
         continue
      }

      #
      # TABLE tags start here
      #
      lappend xml "\t\t<TABLE name=\"$table(TABLE_NAME)\">"

      foreach key [array names table] {
         lappend xml "\t\t\t<$key>$table($key)</$key>"
      }

      foreach columnDef [db columns $table(TABLE_NAME)] {

         set column(TABLE_QUALIFIER) [lindex $columnDef 0]
         set column(TABLE_OWNER)     [lindex $columnDef 1]
         set column(TABLE_NAME)      [lindex $columnDef 2]
         set column(COLUMN_NAME)     [lindex $columnDef 3]
         set column(DATA_TYPE)       [lindex $columnDef 4]
         set column(TYPE_NAME)       [lindex $columnDef 5]
         set column(PRECISION)       [lindex $columnDef 6]
         set column(LENGTH)          [lindex $columnDef 7]
         set column(SCALE)           [lindex $columnDef 8]
         set column(RADIX)           [lindex $columnDef 9]
         set column(NULLABLE)        [lindex $columnDef 10]
         set column(REMARKS)         [lindex $columnDef 11]

         #
	 # COLUMN tags start here
	 #
         lappend xml "\t\t\t<COLUMN name=\"$column(COLUMN_NAME)\">"

         foreach key [array names column] {
            lappend xml "\t\t\t\t<$key>$column($key)</$key>"
         }

         lappend xml "\t\t\t</COLUMN>"
      }
      unset column

      foreach indexDef [db indexes $table(TABLE_NAME)] {

         set index(TABLE_QUALIFIER)  [lindex $indexDef 0]
         set index(TABLE_OWNER)      [lindex $indexDef 1]
         set index(TABLE_NAME)       [lindex $indexDef 2]
         set index(NON_UNIQUE)       [lindex $indexDef 3]
         set index(INDEX_QUALIFIER)  [lindex $indexDef 4]
         set index(INDEX_NAME)       [lindex $indexDef 5]
         set index(TYPE)             [lindex $indexDef 6]
         set index(SEQ_IN_INDEX)     [lindex $indexDef 7]
         set index(COLUMN_NAME)      [lindex $indexDef 8]
         set index(COLLATION)        [lindex $indexDef 9]
         set index(CARDINALITY)      [lindex $indexDef 10]
         set index(PAGES)            [lindex $indexDef 11]
         set index(FILTER_CONDITION) [lindex $indexDef 12]

         #
	 # INDEX tags
	 #
         lappend xml "\t\t\t<INDEX name=\"$index(INDEX_NAME)\">"

         foreach key [array names index] {
            lappend xml "\t\t\t\t<$key>$index($key)</$key>"
         }

         lappend xml "\t\t\t</INDEX>"
      }
      unset index
      lappend xml "\t\t</TABLE>"
   }
   unset table
   lappend xml "\t</SCHEMA>"

   #
   # Obtain the Table data 
   #
   lappend xml "\t<DATA>"

   foreach tableDef [db tables] {

      set table(TABLE_QUALIFIER) [lindex $tableDef 0] 
      set table(TABLE_OWNER)     [lindex $tableDef 1]
      set table(TABLE_NAME)      [lindex $tableDef 2]
      set table(TABLE_TYPE)      [lindex $tableDef 3]
      set table(REMARKS)         [lindex $tableDef 4]

      #
      # Only process normal tables
      #
      if {[string compare $table(TABLE_TYPE) TABLE]} {
         continue
      }

      #
      # Execute SELECT
      #
      db statement data "SELECT * FROM $table(TABLE_NAME)"
      data execute

      #
      # Save column specification
      #
      foreach colSpec [db columns $table(TABLE_NAME)] {
         lappend typeList [lindex $colSpec 3]
         lappend typeList [lindex $colSpec 5]
      }
      array set type $typeList

      #
      # Fetch each row
      #
      lappend xml "\t\t<TABLE name=\"$table(TABLE_NAME)\">"

      while {[data fetch row]} {
         lappend xml "\t\t\t<ROW>"
	 
	 foreach key [array names row] {
	    lappend xml "\t\t\t\t<COLUMN name=\"$key\" type=\"$type($key)\">[Encode $row($key)]</COLUMN>"
	 }

         lappend xml "\t\t\t</ROW>"
      }
      unset row

      lappend xml "\t\t</TABLE>"
   }

   lappend xml "\t</DATA>"
   lappend xml "</ODBC>"

   #
   # Write the XML file
   #

   set fp [open $xmlFile w]
   puts $fp [join $xml "\n"]
   close $fp

   #
   # Cleanup
   #
   db disconnect
}

##
#
# Main Program
#
##
foreach file [glob *.mdb] {
   XMLfromAccess $file [file rootname $file].xml
}
</pre>


The following example XSLT stylesheet shows how the tables and indexes can be created and the data loaded.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 

<xsl:output method="text"/>

<!--
Match the whole XML document
-->
<xsl:template match="/">
  <xsl:apply-templates select="//SCHEMA/TABLE"/>
  <xsl:apply-templates select="//DATA/TABLE/ROW"/>
</xsl:template>

<!--
CREATE TABLE ..  and call the INDEX template
-->
<xsl:template match="TABLE">
  CREATE TABLE <xsl:value-of select="@name"/> (<xsl:apply-templates select="COLUMN" mode="schema_table"/>);
  <!--
  Create all indexes associated with a table
  -->
  <xsl:apply-templates select="INDEX"/>
</xsl:template>

<!--
CREATE TABLE .. (?)
-->
<xsl:template match="COLUMN" mode="schema_table">
  <xsl:value-of select="@name"/>
  <xsl:text> </xsl:text>
  <!--
  Type matchings
  -->
  <xsl:choose>
    <xsl:when test="TYPE_NAME='COUNTER'">INTEGER</xsl:when>
    <xsl:otherwise><xsl:value-of select="TYPE_NAME"/></xsl:otherwise>
  </xsl:choose>
  <xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>

<!--
CREATE INDEX ..
-->
<xsl:template match="INDEX">
  <xsl:if test="string-length(@name)&gt;0">
  CREATE <xsl:choose><xsl:when test="NON_UNIQUE=0">UNIQUE</xsl:when></xsl:choose> INDEX <xsl:value-of select="INDEX_NAME"/> ON <xsl:value-of select="TABLE_NAME"/> (<xsl:value-of select="COLUMN_NAME"/>);
  </xsl:if>
</xsl:template>

<!--
INSERT INTO ..
-->
<xsl:template match="ROW">
  INSERT INTO <xsl:value-of select="../@name"/> (<xsl:apply-templates select="COLUMN" mode="data_table_row1"/>) VALUES (<xsl:apply-templates select="COLUMN" mode="data_table_row2"/>);
</xsl:template>

<!--
INSERT INTO .. (?)
-->
<xsl:template match="COLUMN" mode="data_table_row1">
  <xsl:value-of select="@name"/> 
  <xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>

<!--
INSERT INTO .. (..) VALUES (?)
-->
<xsl:template match="COLUMN" mode="data_table_row2">
  <!--
  Some types need to be quoted
  -->
  <xsl:choose>
    <xsl:when test="@type='CHAR'">'<xsl:value-of select="."/>'</xsl:when>
    <xsl:when test="@type='VARCHAR'">'<xsl:value-of select="."/>'</xsl:when>
    <xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
  </xsl:choose>
  <xsl:if test="position()!=last()">,</xsl:if>
</xsl:template>

</xsl:stylesheet>