Put XML to Use in Access XP

September 17, 2002

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

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

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:

Store table linking information in a local Access table
Export that table to an XML file
Edit the XML file to adjust the path to the mdb file for each user or developer
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
    rst.MoveNext
  Loop
  
  ' Clean up objects
  Set tdf = Nothing
  rst.Close
  Set rst = Nothing
  dbs.Close
  Set dbs = Nothing
  
  TableLinkXML = Err.Number
    
End Function

See All Articles by Columnist Danny Lesandrini

Put XML to Use in Access XP

How to Use the New ImportXML and ExportXML Methods

Putting our XML to Good Use

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos