Programmatically Using XML from Access 2003


In this section, you'll learn programming techniques for working with Access and XML documents. Some of these techniques revolve around critical Access Application object methods for exporting database objects as XML documents from Access database objects and Access projects, as well as importing XML documents into tables in Access database files and Access projects. Other techniques demonstrate basic MSXML programming techniques for retrieving whole XML documents, portions of XML documents, and even revising XML documents. A third area of functionality relates to presenting Access database objects exported as XML documents in a browser using built-in programming techniques. You'll also learn custom extensions of the built-in techniques that enable you to readily create custom representations of XML documents based on objects from either .mdb or .adp files.

Creating and Processing XML for an Access Table

Exporting an XML document based on an Access table normally involves exporting two files ”one containing the data as an XML document and the other containing the XML schema for the XML document. The XML data file has an .xml extension, and the XML schema file has an .xsd extension. Through an extensive array of arguments, the ExportXML method for an Access Application object can create the .xml and .xsd files, as well as perform a wide variety of other functions for generating XML. As you are getting started, you'll need a few of the arguments that this section covers. To complete the transfer of the data from an Access table from one Access file (either an .mdb or an .adp file) to another, you need to invoke the ImportXML method for the Access Application object in the document that reconstructs the Access table from the XML document and schema. Subject to the limitations noted in the "Manually Using XML from Access 2003" section of this chapter, you can use XML to freely exchange Access data between any pair of Access database files or Access projects.

Exporting the XML for a Table with the ExportXML Method

To export a table with the ExportXML method, you need to specify four arguments. Two arguments designate the table, one argument specifies the name of the path and file for the XML document, and the one remaining argument denotes the name of the path and file for the XML schema. You can specify the arguments positionally or by name . The ExportXMLForShippersTable procedure demonstrates the syntax that uses names to specify arguments; this procedure is available in Chapter15.mdb. The ObjectType argument designates the type of database object that can serve as the source for an XML document. The following code sample uses the acExportTable constant to designate a table as a source for the XML document. The DataSource argument is a string value with the name of the table. Depending on the ObjectType argument value, the DataSource argument can name a table, a query, a form, a report, or a SQL Server stored procedure, view, or user -defined function.

Note  

Use the Object Browser to examine the arguments and intrinsic constants for selected argument values with the ExportXML method. For example, the browser reports the following intrinsic constant names for specifying the ObjectType argument: acExportForm , acExportFunction , acExportQuery , acExportReport , acExportServerView , acExportStoredProcedure , and acExportTable .

 SubExportXMLForShippersTable() Application.ExportXML_ ObjectType:=acExportTable,_ DataSource:="Shippers",_ DataTarget:="C:\Access11Files\Chapter15\Shippersp1.xml",_ SchemaTarget:="C:\Access11Files\Chapter15\Shippersp1.xsd" EndSub 

After invoking the ExportXMLForShippersTable procedure, Access adds a pair of files to the C:\Access11Files\Chapter15 folder on your computer. Make sure the folder exists prior to invoking the procedure or change the path in the DataTarget and SchemaTarget arguments. The procedure uses file names of Shippersp1.xml and Shippersp1.xsd so that it will not overwrite files created manually from earlier examples in this chapter.

Viewing and Manipulating an XML Document

Before importing an XML document exported from another Access database file or Access project, it can be useful to examine or even modify the XML document so that the document writes properly to a new database object or appends to an existing one. If the source database object for an XML document and schema match the name of a database object into which you create a new database object based on the XML document, then the new imported object appears with a number after it. For example, if you attempt to create a new Shippers table into a database that already has a Shippers table, the ImportXML method creates a new table named Shippers1 based on the XML document. There are several workarounds for this situation. Three possible solutions include: erasing the old data before appending the new data to a database object; appending the new data to existing data in a database object; and editing the XML document and schema so that the ImportXML method creates a new table with a different name. In any event, two capabilities are helpful. One is to view the contents of an XML document, and the other is to update the contents of an XML document.

Your applications can view and manipulate an XML document through MSXML. As mentioned earlier, MSXML installs automatically with Internet Explorer 6.0, which installs along with Windows XP. You can learn more about MSXML version 4.0 installation and redistribution at this URL:

http://msdn.microsoft.com/library/ default.asp?url=/library/ en-us/xmlsdk /htm/sdk_intro_6g53.asp?frame=true .

When running procedures in an Access module that utilize MSXML, you will need a reference to a version of MSXML. Office 2003 makes available MSXML 5.0. From the References dialog box, you can create a reference for MSXML 5.0 by browsing to MSXML5.DLL in the \Program Files\Common Files\Microsoft Shared\Office11 folder.

MSXML offers a means of viewing and manipulating an XML document via a traditional COM object model interface. Since VBA can reference COM objects, you can use MSXML to process XML documents created by the ExportXML method. The following samples apply the DOMDocument and the IXMLDOMNodeList objects. The DOMDocument object is the top-level pointer at an XML source, such as an XML document or an XML schema. You can use this object to point at any file comprised of XML, such as an XML document, an XML schema, or even an XSLT program. You can use the DOMDocument object to retrieve and create all other XML objects in an XML source. The DOMDocument is an in-memory representation of an XML source. Any XML source is a hierarchical collection of nodes. Nodes within an XML source consist of elements, attributes, CDATA sections, and other components within an XML source.

The pair of procedures in the next listing read an XML document file into memory, and then print the in-memory version of the document to the Immediate window. These procedures, as well as the other procedures in this section, are in Chapter15Receive.mdb. The next pair of procedures depend on a reference to MSXML 5.0 and a global declaration and instantiation of a DOMDocument object. In fact, other samples in this section rely on the prior existence of the reference and DOMDocument object. The syntax for the global declaration and instantiation is on the next line.

 Dimdoc1AsNewMSXML2.DOMDocument50 

The first procedure in the next listing specifies the name of the path and file for an XML document and passes that value to the second procedure. In this case, the target XML document file is the XML document generated in the preceding section ”namely, Shippersp1.xml in the C:\Access11Files\Chapter15\ path. The second procedure, a function procedure, loads the XML document into the doc1 variable in memory as a DOMDocument object. Next, the second procedure assigns the doc1 memory variable to the function's name so that the XML document becomes available through the function call in the first procedure. By isolating the reading of the XML document in a function procedure, this simple application makes it easy to reuse the code for reading an XML document into the doc1 variable in other procedures. When focus returns to the first procedure, the sample prints the XML property for the XML document. This property returns the whole document in this case because it applies to a DOMDocument object. This kind of application is useful when you want to review the content of an XML document, including all its tags.

 'RequiresreferencetoMicrosoftXML,v5.0as 'wellasaglobaldeclarationandinstantiation 'ofdoc1asaMSXML2.DOMDocument50object SubPrintXML() Dimstr1AsString str1= "C:\Access11Files\Chapter15\Shippersp1.xml" 'Printin-memorydocobject Debug.PrintReadXML(str1).XML EndSub     FunctionReadXML(strToXMLAsString) 'LoadXMLfileintodocobject doc1.LoadstrToXML 'Returndoc1object SetReadXML=doc1 EndFunction 

Although the printout of the XML in an XML document is informative because it includes all the tags along with their values, the tags can be distracting. It is sometimes more useful to present just the column values for each row delimited by something, such as a blank line, to separate rows of column values. The next listing illustrates one approach to this task. The sample application consists of three procedures, one of which is the ReadXML function procedure described in the preceding sample. Therefore, the next listing shows just two procedures. The application prints to the Immediate window the contents of each column for successive row values from the Shippersp1.xml document. In between successive rows, the sample prints a blank line. Figure 15-11 displays the output. While the listing applies to the Shippersp1.xml file, it can process the contents of any table from an Access database file or an Access project via the ExportXML method or a manual technique for creating an XML document based on an Access database object. The sample returns the values of elements. Therefore, if your XML document stores its values in attributes, such as MyShippersWithAttributes.xml from the "XML Document Syntax" section of this chapter, you should modify the sample.

click to expand
Figure 15.11: The Shippersp1.xml tag values printed as sets of column values to the Immediate window.

The CallParseAllNodesInATable procedure performs two functions. First, it populates the doc1 variable by calling the ReadXML function with an XML document argument based on the string value in str1 . In the sample listing, the str1 variable points at a path and file for an XML document. Change this for any document that you want to print. Next, the CallParseAllNodesInATable procedure invokes the ParseAllNodesInATable procedure. The first procedure passes a list of nodes for the whole document by applying the childNodes method to the doc1 variable. The ParseAllNodesInATable procedure accepts its argument with a variable named nodelist that has an IXMLDOMNodeList type.

The second procedure has a novel design because it successively calls itself whenever it discovers a node with child nodes, except for the first node. Actually, the procedure performs multiple tests for each node. First, if the current node has a length of more than two nodes to its final element, the procedure prints a blank line. This works well for the XML document based on the Shippers table because each Shippers element has three nodes below it for ShipperID , CompanyName , and Phone values. The feature represents a limitation of the application, which will print a blank row only between the column values of successive rows when there are at least three column values in a table. Next, the procedure starts a loop to iterate through all the nodes in the current node list. When first called, the node list consists of the declaration node and any nodes before the first Shippers element. Each Shippers element contains three nodes in the node list below it. When the procedure finds the beginning of a node list below a Shippers element, the procedure iterates through the nodes in the list with a For Each loop. If the current node has children, then it is not one of the column values in a Shippers element. Therefore, the procedure calls itself with an argument of child nodes for the current node. In this way, the procedure can return all the child nodes for each Shippers element. Similarly, the procedure can return all the nodes within the elements, marking all rows of any XML document created with the ExportXML method.

Note  

The ParseAllNodesInATable procedure uses a Static declaration to track values of bolChild between successive calls of the procedure by itself.

 SubCallParseAllNodesInATable() Dimstr1AsString str1=_  "C:\Access11Files\Chapter15\Shippersp1.xml" ReadXML(str1) ParseAllNodesInATabledoc1.childNodes EndSub     SubParseAllNodesInATable(ByValnodelistAsMSXML2.IXMLDOMNodeList)     Dimnod1AsIXMLDOMNode StaticbolChildAsBoolean     'Skipalineifnodesinalistisgreaterthan1 'andfunctionstartedprocessingchildnodes(rows) 'forrootnode Ifnodelist.length>1AndbolChild=TrueThen Debug.Print EndIf     'Loopthroughnodes;ifnodehaschildnodesget 'thechildnodesandcallfunctioniteratively ForEachnod1Innodelist Ifnod1.hasChildNodes=TrueThen bolChild=True CallParseAllNodesInATable(nod1.childNodes) Else IfbolChild=TrueThen Debug.PrintSpace(5)&nod1.nodeTypedValue bolChild=False Else Debug.Printnod1.nodeTypedValue EndIf EndIf Nextnod1     EndSub 

The ReviseShipperIDValues procedure that appears next updates ShipperID values in the XML document created in the preceding section. The procedure commences by reading the XML document and creating a list of nodes that includes all the ShipperID values from the document. The getTagElementsByTag method extracts just the nodes with ShipperID values from the XML document. Recall that these ShipperID values are 1, 2, and 3. The procedure adds 3 to each ShipperID value by using the nodeTypedValue property for the node. This property represents the value according to the schema for the document, which assigns an Autonumber type to the node. Although the ShipperID nodes have an Autonumber type, you can modify their values within an XML document. After updating the ShipperID values, the procedure closes by saving the in-memory representation to the file from which it came.

 SubReviseShipperIDValues() Dimstr1AsString Dimnlt1AsMSXML2.IXMLDOMNodeList Dimnod1AsIXMLDOMNode     'ReadtableinShippersp1.xmldocument str1= "C:\Access11Files\Chapter15\Shippersp1.xml" ReadXML(str1)     'ReturnjusttheShipperIDvalues Setnlt1=doc1.getElementsByTagName("ShipperID")     'IncrementShipperIDvaluesby3 ForEachnod1Innlt1 nod1.nodeTypedValue=nod1.nodeTypedValue+3 Nextnod1     'SavetheXMLdocumentwithnewShipperIDvalues doc1.Savestr1     EndSub 

Importing an XML Document as an Access Table

The ImportXMLXSD procedure (see the next listing) shows the syntax for three different ways to import an XML document, Shippersp1.xml, into an Access database file. Before running the procedure, delete all previously created tables from Chapter15Receive.mdb. The procedure resides in the same .mdb file. The procedure invokes the ImportXML method with two different argument sets. Actually, the first argument is always the same. It is the name of the path and file for an XML document that the ImportXML method will use as a source for populating an Access table. The second argument is what makes the two invocations inside a Do loop different from the one invocation outside the loop. The two invocations inside the loop specify that the ImportXML method should append the data in the XML document. The difference is that one deletes any prior data from a previously existing Shippers table, and the other does not delete any prior data from the Shippers table already in the database. The third invocation of the method occurs outside the Do loop. In this instance, the syntax attempts to add a new table to the database named Shippers that contains the data in the XML document. If a Shippers table already exists, this invocation of the method will generate a new version of the table named Shippersx , where x represents an integer denoting the version number for the table after the initial version.

The ImportXMLXSD procedure starts by invoking the OpenSchema method for the connection of the current project to return a recordset of metadata about tables in the database for the project. The Do loop mentioned earlier iterates through the rows of the recordset to find a row with a Table_Name column value equal to Shippers . If it finds such a row, it means the database already has a Shippers table. In this case, the procedure follows one of three courses of action based on user replies to two questions presented by invoking the MsgBox function. Two nested If statements handle the logic for offering these options. The first MsgBox function explains that the table already exists, but asks if the user wants to append the data anyway. The attempt to append the data might not succeed for rows in the XML document with the same ShipperID value as a row in the previously existing Shippers table. If the user replies No to the first prompt, a second prompt asks if the procedure should copy the existing data over the existing data in the Shippers table. If the user replies Yes to this second prompt, an ElseIf clause for the inner If statement within the Do loop deletes all rows in the Shippers table before appending the values from the XML document into the table. This append operation will succeed so long as the table structure matches the one specified in the XML schema for the XML argument. If the user replies No to the second prompt, the procedure does not attempt to update the Shippers data with the contents of the XML document.

Note  

A simple error trap allows the procedure to exit gracefully from errors. For example, if a user attempts to add data from the Shippersp1.xml document without erasing the prior data, the procedure presents an error message without losing control. The user can rerun the procedure and erase the prior values if desired.

No matter which option a user selects within the inner If statement in the Do loop, the sample exits the procedure if a version of the Shippers table already exists. On the other hand, if the database for the current project does not contain a Shippers table, the logic transfers control to the last formulation of the ImportXML method. This method creates a Shippers table based on the XML schema for the XML document and populates the table with values from the XML document.

 SubImportXMLXSD() OnErrorGoToImportTrap Dimrst1AsADODB.Recordset     'SearchforShipperstableincurrentproject,and 'appendnewdataifitalreadyexistsor 'copynewdataoverexistingdataor 'ignorenewdata Setrst1=_ CurrentProject.Connection.OpenSchema(adSchemaTables) DoUntilrst1.EOF Ifrst1("Table_Name")= "Shippers" Then IfMsgBox("Tablealreadyexits.Doyouwant " &_  "appendXMLdata?",vbYesNo)=vbYesThen Application.ImportXML_  "C:\Access11Files\Chapter15\Shippersp1.xml",_ acAppendData ElseIfMsgBox("Tablealreadyexits.Doyouwant " &_  "copyXMLdataoverexistingdata?",vbYesNo)=_ vbYesThen CurrentProject.Connection.Execute_ ("DELETE*FROMShippers") Application.ImportXML_  "C:\Access11Files\Chapter15\Shippersp1.xml",_ acAppendData Else MsgBox "Exitingwithoutchanginganything.",vbInformation EndIf ExitSub EndIf rst1.MoveNext Loop     'InsertstructureanddataforShipperstable Application.ImportXML_  "C:\Access11Files\Chapter15\Shippersp1.xml",_ acStructureAndData     'Cleanupobjectsandexit rst1.Close Setrst1=Nothing ExitSub     'Presenterrordescriptioninamessagebox ImportTrap: MsgBoxErr.Description     EndSub 

It is not uncommon in database applications to have to append new data to a table without deleting existing data. One way to demonstrate this capability with the current resources is to alter the Shippersp1.xml document after populating the Shippers table with the document. You can run the ReviseShipperIDValues procedure to make the ShipperID values different than those initially created for the Shippersp1.xml document by the ExportXML method. Then rerun the ImportXMLXSD procedure and select the first prompt to append the data. These actions will extend the initial Shippers table so that the table contains six rows with ShipperID values from 1 through 6. The second attempt to append rows succeeds because the primary key values are different than those already in the table.

Exporting a Parameter Query and Importing Its Data

Parameter queries are a convenient mechanism for allowing users to interact with an application at run time. Your applications can point the ExportXML method at a parameter query. When a user invokes the application to export an XML document and its schema, the ExportXML method can pass along the parameter query prompt for one or more parameter values. After the user responds to any parameter query prompts, the ExportXML method can compose an XML document and matching schema based on the result set from the run time “supplied parameters. You can handle this task more elegantly by creating ADO Parameter objects and managing the assignment of values to the objects at run time. See Chapter 4 for more discussion and samples illustrating how to do this.

The following SQL script shows an Access parameter query. Chapter15.mdb contains this query; the query's name is CustomersOrdersFilter . The query joins select columns from the Customers and Orders tables. At run time, the query prompts for a customer code. This is a five-character field corresponding to a CustomerID column value in the Customers table. Since one customer can have many orders, the query can return multiple rows when the user designates a single customer.

 SELECTCustomers.CustomerID,Orders.OrderID,Orders.OrderDate,Orders.ShippedDate,Orders.Freight,Orders.ShipVia FROMCustomersINNERJOINOrdersONCustomers.CustomerID=Orders.CustomerID WHERE(((Customers.CustomerID)=[CustomerCode?])); 

When an application refers to the CustomersOrdersFilter query in the DataSource argument for the ExportXML method, the procedure presents the query's prompt. Since the DataSource argument names a query, the ObjectType argument should be set to acExportQuery . The ExportCustomersOrdersFilter procedure below illustrates the syntax for creating an XML document and an XML schema named, respectively, CustomersOrdersFilter.xml and CustomersOrdersFilter.xsd. The procedure resides in Chapter15.mdb. If you run this procedure repeatedly and supply different parameter values, it will overwrite the XML document and schema each time.

 SubExportCustomersOrdersFilter()  Application.ExportXML_ ObjectType:=acExportQuery,_ DataSource:="CustomersOrdersFilter",_ DataTarget:="C:\Access11Files\Chapter15\CustomersOrdersFilter.xml",_ SchemaTarget:="C:\Access11Files\Chapter15\CustomersOrdersFilter.xsd"     EndSub 

When you use the ImportXML method to import the result set from a parameter query, your application retrieves the result set for the most recent invocation of the parameter query. In essence, the process captures the result set just as if it were a table. The ImportXMLForQuery procedure to follow illustrates the syntax for capturing the result set from the CustomersOrdersFilter parameter query invoked by the ExportCustomersOrdersFilter procedure. The ImportXMLForQuery procedure, which resides in Chapter15Receive.mdb, will create a new table each time you invoke it, because the procedure has its second argument set to acStructureAndData . By creating a second procedure that is identical, except that it replaces the second argument value with acAppendData , you can append new data from successive invocations of the parameter query to the existing table. This kind of design is particularly appropriate when you need to successively add new data from different time periods. You can run the procedure exporting XML at the end of each day, week, month, or quarter. The procedure importing XML can run with acAppendData as its second parameter on all occasions, except the first time when the application creates the design for the table and initially populates it with data.

 SubImportXMLForQuery() Application.ImportXML_  "C:\Access11Files\Chapter15\CustomersOrdersFilter.xml",_ acStructureAndData EndSub 

Creating .HTM Files to Show Exported XML

One cool feature about the ExportXML method is that it will create an .xsl file and an .htm file that taps the .xsl file. Specify the .xsl file with the ExportXML method's PresentationTarget argument. Whenever you specify the PresentationTarget argument, you also must assign a value to the ObjectType and DataSource arguments. Recall that the DataSource argument designates an .xml file containing the XML document. The .htm file generated by the ExportXML method contains a script that writes over itself. The script requires a version of MSXML on the client workstation to support use of the Load method. The code inside the script transforms the contents of the .xml file for display in a browser according to the code inside the .xsl file. When users attempt to open the .htm file, they automatically invoke the script that displays all the column values stored in the XML document.

The ExportXMLForShippersTableInBrowser procedure illustrates the syntax for the PresentationTarget argument. Notice that the argument points to an .xsl file in the same folder as the .xml file. The use of the SchemaTarget argument in this context is optional. That's because the primary purpose of the procedure is to generate the files to view the contents of the .xml file in a browser with an HTML table instead of the tagged format that is characteristic of XML documents. The ExportXMLForShippersTableInBrowser procedure generates four files. Three are specified in the arguments for the ExportXML method. The fourth file has an .htm extension and a filename that matches the .xml file. This .htm file contains the script described in the preceding paragraph.

 SubExportXMLForShippersTableInBrowser()  Application.ExportXML_ ObjectType:=acExportTable,_ DataSource:="Shippers",_ DataTarget:="C:\Access11Files\Chapter15\Shippersp2.xml",_ SchemaTarget:="C:\Access11Files\Chapter15\Shippersp2.xsd",_ PresentationTarget:="C:\Access11Files\Chapter15\Shippersp2.xsl"     EndSub 

The HTML table that appears when a user opens the Shippers2.htm file in a browser is the same as the one shown in Figure 15-9. Refer back to that illustration to note a couple of features of the table. First, it shows data for all the columns. Second, it has very basic formatting. You can create your own .xsl file with more elaborate formatting that shows just selected columns. Then, with a slightly edited version of the script in the .htm file generated by the ExportXML method, you can cause the .htm file to open with the custom formatting in your .xsl file as well as any column selection that you specify. When modifying automatically generated files, I find it convenient to create new versions of the .xsl and .htm files with custom code. It is easy to reuse the .xml generated by the ExportXML method.

The following listing shows the XSLT script for the Shippersp21.xsl file. This file contains my custom transformation for showing the Shippersp2.xml file in a browser. The script contains two template elements. The first template lays out the overall format for the HTML page. Notice that it contains opening and closing HTML tags. This template references the XML document's root node in its match pattern ( match = "/") . Within the BODY section of the HTML page, the XSLT code designates a table with two columns ”one with a heading of Shippers and the other with a heading of Phone . The Shippers heading is for the CompanyName element values in the XML document. A STYLE tag in the HEAD section of the HTML page designates a background color of gray for the table heading. The second template references the Shippers element in the XML source. This second template specifies the values to extract for the columns in the table, as well as the formatting for those columns. For example, in the first column it shows the CompanyName element from the Shippers elements within the XML source and the Phone element in the second column. In addition, Phone column values will appear with bold and italic font settings, but the CompanyName column values will show with the default font settings for the HTML page. Conspicuous by its absence is the ShipperID element. The values for this element will not appear in browsers using this transformation for the Shippersp2.xml file.

 <?xmlversion="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:templatematch="/"> <HTML> <HEAD> <TITLE></TITLE> <STYLE>TH{background-color:Gray}</STYLE> </HEAD> <BODY> <TABLEborder='1'style='width:300;'> <TR><THcolspan='2'>Shippers</TH></TR> <TR><TH>CompanyName</TH><TH>Phone</TH></TR> <xsl:apply-templatesselect='dataroot'/> </TABLE> </BODY> </HTML> </xsl:template> <xsl:templatematch="Shippers"> <TR> <TD><xsl:value-ofselect='CompanyName'/></TD> <TD><B><I><xsl:value-ofselect='Phone'/></I></B></TD> </TR> </xsl:template> </xsl:stylesheet> 

Now that we have a custom .xsl file, we also need an .htm file that applies the transformation in the .xsl file to the .xml file generated by the ExportXMLForShippersTableInBrowser procedure. One especially easy way to compose the required .htm file is to edit the .htm file generated by the procedure and save the edited file with a new name, such as Shippersp21.htm. In fact, all you have to do is edit one line. The following script shows this line in bold font. The original version of the line passed Shippersp2.xsl as an argument to the LoadDOM procedure. The edited version passes the new custom XSLT transformation, which resides in Shippersp21.xsl. This file should reside in the same folder as the .xml document.

As you can see, the script in .htm consists of three procedures. The ApplyTransform procedure is the main procedure that starts whenever a user loads a new copy of the page in a browser. This procedure invokes the two other procedures. The first of these instantiates a version of MSXML. Next, the procedure uses the instance of MSXML to load the Shippersp2.xml and Shippersp21.xsl files into memory. In its final line, the ApplyTransform procedure invokes the Write method of the Document object for the HTML page to write the .xml file transformed by the .xsl file over the script.

Note  

The script in the .htm file wipes itself out after loading. This is why you have to reload the page to reflect a change in your XSLT transformation. You cannot simply refresh the page, because the script to apply a new XSLT transformation is no longer available after a page loads.

 <HTMLxmlns:signature="urn:schemas-microsoft-com:office:access"> <HEAD> <METAHTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/> </HEAD> <BODYONLOAD="ApplyTransform()"> </BODY> <SCRIPTLANGUAGE="VBScript"> OptionExplicit     FunctionApplyTransform() DimobjData,objStyle     SetobjData=CreateDOM LoadDOMobjData, "Shippersp2.xml"     SetobjStyle=CreateDOM  LoadDOMobjStyle, "Shippersp21.xsl"  Document.Open "text/html","replace" Document.WriteobjData.TransformNode(objStyle) EndFunction     FunctionCreateDOM() OnErrorResumeNext DimtmpDOM     SettmpDOM=Nothing SettmpDOM=CreateObject("MSXML2.DOMDocument.5.0") IftmpDOMIsNothingThen SettmpDOM=CreateObject("MSXML2.DOMDocument.4.0") EndIf IftmpDOMIsNothingThen SettmpDOM=CreateObject("MSXML.DOMDocument") EndIf     SetCreateDOM=tmpDOM EndFunction     FunctionLoadDOM(objDOM,strXMLFile) objDOM.Async=False objDOM.LoadstrXMLFile If(objDOM.ParseError.ErrorCode<>0)Then MsgBoxobjDOM.ParseError.Reason EndIf EndFunction     </SCRIPT> </HTML> 

Figure 15-12 shows the table in a browser generated by Shippersp21.htm invoking Shippersp21.xsl to transform Shippersp2.xml. You can best appreciate the impact of the custom XSLT file by contrasting Figure 15-12 with Figure 15-9. Recall that Figure 15-9 shows the Shippers table with the standard XSLT transformation provided by Access. The custom XSLT selects a subset of the columns, which exclude ShipperID , and it applies special formatting in the table header area as well as in the Phone column.

click to expand
Figure 15.12: The table that appears when a browser opens Shippersp21.htm.



Programming Microsoft Office Access 2003
Programming MicrosoftВ® Office Access 2003 (Core Reference) (Pro-Developer)
ISBN: 0735619425
EAN: 2147483647
Year: 2006
Pages: 144
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net