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
 }

--

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>