Export and FTP XML Files from Access XP

Thursday Sep 19th 2002 by Danny Lesandrini

Access XP now simplifies the import and export of XML, but how will you put this new feature to work? Danny Lesandrini shows how current development issues can be solved with the new ExportXML method and some simple FTP code.

download code for this article

Access XP now simplifies the import and export of XML, but how will you put this new feature to work? Perhaps the question of how to make good use XML will become clearer as your database applications begin to interact with .NET projects, but you don't have to wait. In the following article, Danny Lesandrini shows how current day development issues can be solved with the new ExportXML method and some simple FTP code.

In my last article I explained how to use the new ImportXML and ExportXML methods in Microsoft Access XP. In that example, an XML file with table linking information was imported using the ImportXML function each time the app was started. As I pointed out, the same results could be reproduced in Access 97 using a delimited text file. So, although that example showed how to use the new feature, it really wasn't a good example of why to use XML.

In this article I'll demonstrate how to use the Access XP XML export in an example that better represents the primary benefit of Extensible Markup Language. The strength of XML is that it standardizes the way developers work with data, making it easy to transfer information between database management systems, such as Access, SQL Server and Oracle. XML will also make your application transferable to the web and non-windows platforms.

Ever since I heard that Access XP would include the ability to import and export data in XML format, I've been keeping my eyes open for a business problem that is solved with XML. The idea for this example came from a question posted to an Internet newsgroup about how to move Access data across platforms to a Linux server. Consider how this might be accomplished.

FTP your Access data using XML
I once reproduced an FTP utility based on a Visual Basic tutorial that was relatively complicated. It contained over 700 lines of code and required the Microsoft Internet Transfer Control. For this project I was looking for a simpler way to FTP files, and I found it at The Access Web (http://www.mvps.org/access/) hosted by Dev Ashish. As we will soon see, Dev's routine requires only a few simple lines of code.

The process of exporting and sending data requires the following steps:

  1. Create a query that represents the data you wish to transfer.
  2. Output to an .XML file using the ExportXML method
  3. Create an .scr file containing the necessary FTP commands
  4. Execute FTP.exe with the .scr file using the Shell command
Create the query
In my example, I'm using a simple table taken from an old help desk application I had laying around. The table, HelpDeskIssue, contains a field named DateCreated which we will use for our filter.

Demo application main form

Each time a new start date or end date is selected, the function UpdateSQL replaces the query's SQL with the parameters chosen by the user. The form's labels are refreshed to reflect these choices.

Private Sub UpdateSQL()
  On Error Resume Next

  m_sSQL = " SELECT * FROM HelpDeskIssue " & _
           vbCrLf & " WHERE DateCreated BETWEEN " & _
           vbCrLf & " #" & txtStart & "# AND #" & txtEnd & "#"
  CurrentDb.QueryDefs("qryExportFTP").sql = m_sSQL

  lblSQL.Caption = "SQL:" & vbCrLf & m_sSQL
  lblMsg.Caption = "Ready"
End Sub

Export the data using ExportXML
Exporting the data is actually the simplest process in the entire project. The only catch is that you will want to check to see if the file already exists, and if so, delete it prior to running the ExportXML function. You'll notice that in this example the query, "qryExportFTP", is always used to create a file that is always named "AccessEXP.xml". In most cases it will make more sense to allow for the export of many different queries and to implement a naming scheme that gives each XML file a meaning name.

  sXMLPath = CurrentProject.Path & "\AccessEXP.xml"

  If Dir(sXMLPath) <> "" Then Kill sXMLPath
  ExportXML acExportQuery, "qryExportFTP", sXMLPath

Create an .scr file of FTP commands
This part of the process was not at all intuitive to me and I simply followed the directions given by Dev Ashish. I'm not sure of the significance of the .scr extension, but it serves as an FTP batch file of commands that are fed to the FTP.exe in the Shell command.

Sample .scr file contents in Notepad

Even if you're not familiar with basic FTP, these commands are not all that complicated to understand. After setting the path to the local directory, you open the desired FTP server with an anonymous login. (You could open a secure FTP server by providing a valid user name and password.) Once you have changed to the correct directory on the FTP server, set the transfer method to binary and copy the file with the "put" command.

These commands are written to the .scr batch file using the VBA Print method. My demo form allows the user to provide an FTP server and destination directory. For this example, it is assumed that an anonymous login is allowed, and will always be used.

  sAppPath = GetShortAppPath
  sSCR = sAppPath & "AccessFTP.scr"
  sEXPFile = sAppPath & "AccessEXP.xml"
  sEXPFile = GetShortFileName(sEXPFile)
  sPutFile = "Put " & sEXPFile & " AccessEXP.xml"

  iFreeFile = FreeFile
  Open sSCR For Output As iFreeFile
  Print #iFreeFile, "lcd " & sAppPath
  Print #iFreeFile, "open " & txtFTPServer
  Print #iFreeFile, "anonymous"
  Print #iFreeFile, "YourName@hotmail.com"
  Print #iFreeFile, "cd " & txtDirectory
  Print #iFreeFile, "binary"
  Print #iFreeFile, sPutFile
  Print #iFreeFile, "bye"
  Close #iFreeFile

Execute with the Shell command
The last bit of business is to locate the path to FTP.exe and execute the Shell command with our newly created .scr file. You'll notice that I found it necessary to convert the file paths with the API call named GetShortPathName. This is necessary because the FTP program could not handle long file names.

  Public Sub ExportFTP()
    Dim sSCR As String, 
    sDir As String, sExe As String

    sSCR = CurrentProject.Path & "\AccessFTP.scr"
    sSCR = GetShortFileName(sSCR)

    sDir = Environ$("COMSPEC")
    sDir = Left$(sDir, Len(sDir) - Len(Dir(sDir)))
    sExe = sDir & "ftp.exe -s:" & sSCR

    Shell sExe, vbMaximizedFocus
End Sub

Since the Shell command is executed with the option vbMaximizedFocus, a dos window appears briefly with the FTP commands as they execute. You can pause the window (if you're quick) by pressing the Pause/Break key on your keyboard.

Example of FTP commands executing in a dos window

The demo code above points to an active FTP server at my web site that currently accepts anonymous logins. The form includes a hyperlink to the default folder so you can verify that the program actually transfers your XML file. This FTP site will remain available until it becomes necessary to close it.

As you can see, the code to export and FTP data from Access XP is not complicated. In fact, with the exception of the code to handle long file names, it's downright trivial. Even so, don't let its brevity diminish your appreciation for what is accomplished in the code above. Whether this process is initiated by a user or is kicked off nightly with the Windows Scheduler, it represents a major step forward for Access database applications.

With this process, Access XP programs are poised to participate with applications designed and created with .Net. Also, as mentioned earlier, the ability to move Access data across platforms using XML simplifies its integration with RDBMs such as Oracle. No doubt, as XML continues to gain momentum in the application development community, we will find new ways to use XML data to integrate Microsoft Access XP with other programs and the Internet.

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