You’ve heard that XML is easy to import and export from Access 2002, but you have yet to come up with a good use for it. Danny Lesandrini solves two problems with Access 2002’s XML support: dynamic file linking and moving data across applications.
With Access 2002, Microsoft has provided support for the hottest of new technologies—XML. That’s all well and good, but what use is it? In this article, I’ll show you two different ways that you can use Access 2002 and XML to solve problems. After a quick review of the technology, for instance, I’ll show you how to use XML to re-link files.
A quick review
Instead of simply repeating what the Help file says about using XML from Access 2002, I’ll demonstrate how easy it is to use. The following subroutine exports the contents of a table named LinkedTables to a file and then re-imports it. The default behavior for the export method (when called from the Access object) is to overwrite any existing XML file with the same name. On the other hand, the default behavior for importing is to leave any table with the same name in place and create a second table, named with an ordinal suffix. In the following code, I’ve used the flag cOverWrite to cause the original table to be overwritten during my re-import:
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 Access Help file contains little more information, except for enumerating all of the available options when exporting data (and if you want to better understand the details of XML, XSL, and XSD files, you’ll need to invest in one of the many good XML books on the market).
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 (see Figure 1).
Figure 1
The resulting XML file contents will depend on 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. Figure 2 shows an example of how the LinkedTables XML looks when opened in Internet Explorer.
Figure 2
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, your user interface will have to include functions to locate the data file and refresh the links. Over the years, I’ve seen many creative ways to accomplish this. Access 2002’s XML provides another way to solve the problem.
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 folders, making the rollout of new client mdbs virtually seamless. If you want to move the location of the mdb files on the network share, you only need to replace the text file in the client’s directory.
Of course, this could also be done with a text file or Excel spreadsheet, but XML provides a more readable way of storing data with its own description. And, remember, we want to put our ImportXML and ExportXML tools to work.
The table containing the information about linked tables in our database is quite simple (see Figure 3). Since the client mdb file may link to multiple back-end databases, each table records its own mdb path.
Figure 3
Once the table is populated with table names and appropriate paths, you can export it to an XML file using the code from the start of this article. That file can now be edited using Notepad to modify the TablePath node text to reflect the correct path from the user’s computer and kept in the same folder with the client mdb file.
Finally, you must create a new method 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. Here’s the code:
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
Set dbs = DBEngine(0)(0)
sAppPath = application.CurrentProject.Path & "\"
sXMLPath = sAppPath & "LinkedTables.xml"
ImportXML sXMLPath, acStructureAndData, cOverWrite
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
sCriteria = "Name='" & sTableName & "'"
If DCount("*", "MsysObjects", sCriteria) Then _
dbs.TableDefs.Delete sTableName
Set tdf = dbs.CreateTableDef(sTableName)
tdf.Connect = sConnect
tdf.SourceTableName = sTableName
dbs.TableDefs.Append tdf
End If
rst.MoveNext
Loop
Set tdf = Nothing
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
TableLinkXML = Err.Number
End Function
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 accompanying Download file includes an additional, alternate method for linking tables using XML named TableLinkXMLAlt. That code doesn’t require importing the XML file contents into a local table but just uses standard XML processing. While that eliminates the need to use ImportXML, since the data isn’t transferred to a table it can’t be integrated with the rest of your data.
FTP your Access data using XML
This technique shows how a common Access problem can be solved with XML by using ImportXML and ExportXML to handle a problem for which they’re uniquely qualified. My solution shows how to automate data transfer via the Internet, sending Access-generated XML files via FTP from a VBScript file.
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 also makes your application transferable to the Web and non-Windows platforms. 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.
I once reproduced an FTP utility based on a Visual Basic tutorial that was relatively complicated. The utility contained more than 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 ( www.mvps.org/access), hosted by Dev Ashish. Dev’s routine requires only a few simple lines of code.
The process of exporting and sending data requires the following steps:
- Create a query that represents the data you wish to transfer.
- Output to an XML file using the ExportXML method.
- Create an .scr file that contains the necessary FTP commands.
- Execute FTP.exe with the .scr file by using the Shell command.
Create the query
In my example, I’m using a simple table taken from an old help desk application that I had laying around. The table, HelpDeskIssue, contains a field named DateCreated that I’ll use to control which data is to be sent. Figure 4 shows the application that manages the table. This application, by moving the data to a Web server, makes it accessible to be downloaded to other applications (including Access through the ImportXML command). The data can also be processed by non-Access applications using standard XML tools—or even by non-Windows applications.
Figure 4
When a new start date or end date is selected in the form, the function UpdateSQL creates a query that selects the data between the user’s dates. The form’s labels are refreshed to reflect these choices. This is the function that generates the SQL statement:
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 from the HelpDeskIssue table is actually the simplest process in the entire project, thanks to Access 2002’s XML support. The only catch is that you’ll want to check to see whether 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, creates a file that’s 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 meaningful name. Here’s my simple version of the code:
sXMLPath = CurrentProject.Path & "\AccessEXP.xml" If Dir(sXMLPath) <> "" Then Kill sXMLPath End If ExportXML acExportQuery, "qryExportFTP", sXMLPath
Create an .scr file of FTP commands
This part of the process wasn’t 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 (see Figure 5).
Figure 5
Even if you’re not familiar with basic FTP, these commands aren’t all that difficult 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’ve changed to the correct directory on the FTP server, the code sets the transfer method to binary and copies the file with the “put” command.
I write these commands to the .scr batch file using the VBA Print method. My sample form allows the user to provide an FTP server and destination directory. For this example, I’ve 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 the newly created .scr file. You’ll notice that I found it necessary to convert the file paths using the API call GetShortPathName. This is necessary because the FTP program that comes with Windows can’t handle long filenames:
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, displaying the FTP commands as they execute (see Figure 6). You can pause the window (if you’re quick) by pressing the Pause/Break key on your keyboard.
Figure 6
This sample code 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 that you can verify that the program actually transfers your XML file. This FTP site will remain available until it becomes necessary to close it, so try it out for yourself.
As you can see, the code to export and FTP data from Access 2002 isn’t complicated. In fact, with the exception of the code to handle long filenames, it’s downright trivial. Even so, don’t let its brevity diminish your appreciation for what’s accomplished in the code shown here. 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 2002 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, you’ll find new ways to use XML data to integrate Microsoft Access 2002 with other programs and the Internet.
