Export and FTP XML Files from Access XP

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.

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.

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.

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.

Conclusion

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.