Put XML to Use in Access XP

download code for this article

So you’re dying to use XML in your applications, but you don’t know where to begin.
You’ve heard that XML is easy to import and export from Access XP, but you have
yet to come up with a good use for it. The following article explains how to use
XML files to simplify dynamic table linking.

How to Use the New ImportXML and ExportXML Methods

Instead of simply repeating what the help file says about these new methods, I’ll
demonstrate how easy they are to use. The sub routine below exports the contents
of a table named LinkedTables to a file and then re-imports it back again. The default
behavior for the export method (when called from the Access object) is to overwrite any
existing XML file, whereas the default behavior for the import is to leave the original
table and create a second table, named with an ordinal suffix. In other words, if the
code below didn’t include the flag cOverWrite, then the original table would remain intact
and an additional table named LinkedTables1 would be created. For this example, we
will overwrite the existing table.

Public Sub ImportExportExample()
  Dim sXMLPath As String
  Const cOverWrite As Integer = 1
  sXMLPath = "D:\KBSearch\LinkedTables.xml"
  ' ExportXML ObjectType, DataSource, DataTarget
  ExportXML acExportTable, "LinkedTables", sXMLPath

  ' ImportXML DataSource, DataTransform, OtherFlags
  ImportXML sXMLPath, acStructureAndData, cOverWrite

End Sub

Importing and exporting data from tables in Access is that easy. The help file contains
little additional information, except for enumerating all the options available when
exporting data. If you want to better understand the details of XML, XSL and XSD files,
you would do well to invest in one of the many good XML books.

Data may also be imported and exported using the Access user interface through the File menu.
To import an XML file, select File | GetExternalData | Import, choose “XML Documents” from the
“File of Type” drop down, and navigate to your file, just as you would when importing a text or
Excel file. On the other hand, when exporting a table to XML format, the user is prompted with
a new interface for selecting standard and advanced file export options.

New user interface for exporting a table to XML

The resulting XML file contents will depend upon the options selected. The advanced options
allow you to include the schema information, either embedded within the XML document or as
a separate XSD document. You can even create a separate XSL document containing formatting
information. Below is an example of how the LinkedTables XML looks when opened in Internet Explorer.

XML output of the LinkedTables export in Internet Explorer

Putting our XML to Good Use

In order for an Access application to be truly useful in a multi-user environment, the data
must be separated from the user interface and placed on a network share, common to all users.
Unless the application developer, and all users, have their network shares defined in the same
way, it will be necessary for the user interface to locate the data file and refresh the links.
Over the years I’ve seen many creative ways to accomplish this, and though XML is not required,
it does fit this need quite nicely.

The process is simple:

  1. Store table linking information in a local Access table
  2. Export that table to an XML file
  3. Edit the XML file to adjust the path to the mdb file for each user or developer
  4. Create a startup routine that imports the XML and refreshes table links

The advantage of this process to the developer is that once you edit the XML files, you never
need think about them again. Each time you move the application front-end from client to development
machines, the table links are refreshed automatically. If some users map drives differently to the
same network share, they too will avoid the need to re-link tables each time a new client is rolled out.
Their unique table link paths can be permanently stored in their client folder, making the rollout of
new client mdbs virtually seamless. Of course, this could also be done with a text file or Excel
spreadsheet, but remember, we want to put our ImportXML and ExportXML tools to work.

Our table containing the information about linked tables in our database is quite simple. The figure
below shows how simple the table can be. Since the client mdb file may link to multiple back-end
databases, each table records its own mdb path.

XML output of the LinkedTables export in Internet Explorer

Once the table is populated with table names and appropriate paths, it may be exported to an
XML file as described above. (The above code may even be used to create the necessary XML file.)
That file may now be edited using Notepad to modify the TablePath node text to reflect the correct
path. It is then kept in the same folder where the client mdb file resides.

Finally, a new method is created to import this XML file and replace the existing LinkedTables table,
which is then used to refresh all linked tables. This method can be executed in the AutoExec macro
or simply called from the startup form. (See Listing 1)

The code to refresh links is similar to that used in previous versions of Access and I won’t go into a
detailed explanation here. The code for this article is available for download and includes an additional,
alternate method for linking tables using XML named TableLinkXMLAlt that doesn’t require importing the
XML file contents into a local table. Though it is a better solution, it removes the need for the use of
the ImportXML() method, making this entire article obsolete.

So, if this new XML feature of Access isn’t needed to perform the above described dynamic table linking
functionality, then why did we just go through this exercise? We did it to demonstrate how to implement
XML data transfer in Microsoft Access XP. The next step is to find a really useful application for ImportXML
and ExportXML. I’m currently working on one idea for automating data transfer via the Internet, sending
Access generated XML files with FTP after hours from a VBScript file. Hopefully that topic will make it
into a future Smart Access article. In the mean time, I welcome your comments, suggestions and ideas on
how you would like to use XML with your Access XP applications.

This article originally appeared in the January 2002 issue of Smart Access, Pinnacle Publishing, Inc. (c) All rights reserved.

Code listing for the TableLinkXML() Method

Public Function TableLinkXML() As Boolean
  Dim sTableName As String
  Dim sAppPath As String
  Dim sXMLPath As String
  Dim sCriteria As String
  Dim sConnect As String
  Dim rst As ADODB.Recordset
  Dim tdf As DAO.TableDef
  Dim dbs As DAO.Database
  Const cOverWrite As Integer = 1
  On Error Resume Next

  ' Database object used for creating TableDef objects
  Set dbs = DBEngine(0)(0)

  ' Import the table linking info from our xml file
  sAppPath = application.CurrentProject.Path & "\"
  sXMLPath = sAppPath & "LinkedTables.xml"
  ImportXML sXMLPath, acStructureAndData, cOverWrite

  ' Load that table into a recordset and use it to loop
  ' through the list, linking tables.
  Set rst = New ADODB.Recordset
  rst.Open "LinkedTables", CurrentProject.Connection

  Do Until rst.EOF
    If rst!PerformLink = True Then
      sTableName = rst!TableName
      sConnect = ";DATABASE=" & rst!TablePath
      ' If the table link exists, then delete it
      sCriteria = "Name='" & sTableName & "'"
      If DCount("*", "MsysObjects", sCriteria) Then _
        dbs.TableDefs.Delete sTableName
      ' Create a new tabledef and set it's properties
      Set tdf = dbs.CreateTableDef(sTableName)
      tdf.Connect = sConnect
      tdf.SourceTableName = sTableName
      dbs.TableDefs.Append tdf
    End If
  ' Clean up objects
  Set tdf = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  TableLinkXML = Err.Number
End Function

This article originally appeared in the January 2002 issue of Smart Access, Pinnacle Publishing, Inc. (c) All rights reserved.

See All Articles by Columnist
Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles