Export and FTP XML Files from Access XP
September 18, 2002download code for this article
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.