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:
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.
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.