Special Edition Using Microsoft Office Access 2003

WSR tries to create classes for any valid WSDL documents. If WSR determines the WSDL document isn't valid or WSR can't process it, the Search Result text box displays a "Your search returned no results" message. In rare cases, you receive an error message when you click WSR's Add button. Some WSDL documents generate classes that won't compile. As an example, the WSDL documents that Microsoft SQLXML 3.0 generates for Web services created from SQL Server 2000 stored procedures produce VBA classes that generate compilation errors. This is an important issue, because SQLXML 3.0 makes providing data-driven Web services a quick and easy process. A typical application for SQL Server 2000 Web services is delivering up-to-date product catalogs or similar reference data via the Internet to Access order processing applications designed for sales agents or customers.

Note

Future versions of SQLXML, WSR, or both might correct the problem you solve in the following sections. The techniques you learn, however, let you rather than WSR manage the processing of any SOAP response message by writing relatively simple VBA procedures.

If the capability to consume problematic Web services is important to your Access application, you must modify the code of WSR-generated classes. Following are the typical steps to handle defective WSR classes for complex types:

1. Comment the statement(s) that prevent compilation. In most cases, removing the statements causes execution of one or more Web methods to fail.

2. Add code to the form's class module to create a Web service proxy and invoke one of the methods you plan to use.

3. Execute the Web method. If you encounter runtime errors or the service doesn't return the data you expected, the problem is likely to be deserializing the soap response message to one or more of the custom types specified in the WSML file.

   Note

   If WSR doesn't interpret the WSDL document correctly or can't generate a VBA-compliant type, the offending struct_ClassName class module often contains members of the MSXML2.IXMLDOMNodeList type. IXMLDOMNodeList is the default type returned by the SoapClient30 object.

4. Delete or comment all str_WSDL = ... lines to return an empty string, and delete all WSR classes except clsws_ServiceName.

5. Determine the type returned by clsws_ServiceName's Public Function wsm_FunctionName(Parameters...) As Type. If Type isn't [MSXML2.]IXMLDOMNodeList, change it to this value. (Adding the MSXML2 type library name is optional).

6. Compile the code and remove statements that generate compile errors.

Completing the preceding steps disables WSR's complex type handling features and requires writing VBA code to process the response message that's contained in the IXMLDOMNodeList instance. You can handle any complex SOAP response message, regardless of its structure, with the IXMLDOMNodeList class.

Understanding the XML Document Object Model

Working with the IXMLDOMNodeList object requires some familiarity with the XML Document Object Model (XML DOM). The XML DOM represents any well-formed XML document as a set of elements (nodes) that contain information (text), other nodes (called child nodes), or both. You extract the document's text values to populate controls or tables with an XML parser. The MSXML2 library (Microsoft XML v5.0, Msxml5.dll), which Office 2003 installs in your \Program Files\Common Files\Microsoft Shared\Office11 folder, was Microsoft's latest COM-based XML parser when this book was written.

Note

MSXML2 is best suited for processing relatively small XML documents, because it loads at least one copy of the entire document into memory. Other XML parsers, such as the Simple API for XML (SAX), don't require loading the document into memory but have a more complex, event-driven programming model.

As its name implies, the IXMLDOMNodeList class provides access (a pointer) to XML DOM nodes in a document from a list. Each node is a member of the IXMLDOMNode class. You iterate the list in a set of nested For...Next loops that return IXMLDOMNode members at each level of the document's hierarchy. The IXMLDOMNode.text property at the lowest level of the hierarchy returns individual data values.

Following is an abbreviated example of a SOAP response message returned by a demonstration Web service (Alpha) that's generated from one of four SQL Server stored procedures exposed as Web methods:

<?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sqltypes=".../SQLServer/2001/12/SOAP/types" xmlns:sqlmessage=".../SQLServer/2001/12/SOAP/types/SqlMessage" xmlns:sqlresultstream=".../SQLServer/2001/12/SOAP/types/SqlResultStream" xmlns:tns="http://www.oakleaf.ws/SQLXML/Alpha"> <SOAP-ENV:Body> <tns:GetTop10Response> <tns:GetTop10Result xsi:type="sqlresultstream:SqlResultStream"> <sqlresultstream:SqlXml xsi:type="sqltypes:SqlXml" sqltypes:IsNested="false"> <SqlXml> <row> <SKU>DVDP0345</SKU> <Category>DVD Players</Category> <Brand>Onkyo</Brand> <Model>DV-S939</Model> <Description> DVD-Audio/Video/CD Player with Progressive </Description> <NetPrice>1275</NetPrice> <Quantity>516</Quantity> </row> <!-- Eight rows not shown --> <row> <SKU>DVDP0009</SKU> <Category>DVD Players</Category> <Brand>Aiwa</Brand> <Model>XD-DW5</Model> <Description> Portable DVD player with 5.8-inch Color Screen </Description> <NetPrice>375</NetPrice> <Quantity>514</Quantity> </row> </SqlXml> </sqlresultstream:SqlXml> <sqlresultstream:SqlResultCode xsi:type="sqltypes:SqlResultCode" sqltypes:IsNested="false"> 0 </sqlresultstream:SqlResultCode> </tns:GetTop10Result> </tns:GetTop10Response> </SOAP-ENV:Body> </SOAP-ENV:Envelope>

The Alpha Web service delivers a catalog from Alpha Electronics, Inc., a fictitious consumer electronics distributor. The GetTop10Result Web method returns a list of Alpha's 10 most expensive consumer electronics products in one or more of six categories as an sqlresultstream message, which contains first-level SqlXml and SqlResultCode child nodes. The SqlXml node contains a collection (array) of second-level child row nodes, which contain the third-level data elements. The SqlResultCode node has a value of 0 if the request is successful or an error number if it isn't. In the event of an SQL Server error, the response message includes a serialized sqlmessage object, which contains a detailed description of the problem.

Navigating the IXMLDOMNodeList

Before you can write the VBA code necessary to populate a list box or table with data, it's a good practice to perform a test iteration of the IXMLDOMNodeList object to determine the level in the hierarchy at which you obtain a useful representation of the data. "Walking the nodes" also lets you explore the structure of XML data in a SOAP response message when you can't infer the structure from the WSDL document's schema.

Note

Listing 31.1 is the VBA code for a general-purpose subprocedure that prints to the Immediate window the level, nodeName, and text properties of an IXMLDomNodeList object you pass as a parameter. You can use this code as a model for the procedures you write to populate controls or tables from the SOAP response message, as illustrated in the next section. You can add additional For...Next loops if the message contains more than three levels of child nodes.

Listing 31.1 This VBA subprocedure lets you use the Immediate window to explore SOAP response message structure and data.

Public Sub PrintNodeList(nodList As MSXML2.IXMLDOMNodeList) 'Generates an indented list of node names and 'values in the Immediate window 'This procedure is limited to three levels of child nodes Dim intNodes As Integer Dim intMaxNodes As Integer Dim intMaxLength As Integer Dim intChild1 As Integer Dim intChild2 As Integer Dim intChild3 As Integer Dim domNode As MSXML2.IXMLDOMNode Dim domChild1 As MSXML2.IXMLDOMNode Dim domChild2 As MSXML2.IXMLDOMNode Dim domChild3 As MSXML2.IXMLDOMNode 'Set the maximum number of child nodes printed at each level intMaxNodes = 6 'Set the maximum length of node text strings intMaxLength = 100 With nodList 'The parent node If .length > 0 Then For intNodes = 0 To .length - 1 'List nodes (level 0) Set domNode = .Item(intNodes) With domNode Debug.Print "Level 0: nodeName(" & intNodes & ") = " _ & .nodeName Debug.Print "Level 0: text(" & intNodes & ") = " _ & Left$(.Text, intMaxLength) If .hasChildNodes Then 'First-level child nodes For intChild1 = 0 To .childNodes.length - 1 If intChild1 > intMaxNodes Then Exit For End If Set domChild1 = .childNodes(intChild1) With domChild1 Debug.Print " Level 1: nodeName(" & intChild1 & ") = " _ & .nodeName Debug.Print " Level 1: text(" & intChild1 & ") = " _ & Left$(.Text, intMaxLength) If .hasChildNodes Then 'Second-level child nodes For intChild2 = 0 To .childNodes.length - 1 If intChild2 > intMaxNodes Then Exit For End If Set domChild2 = .childNodes(intChild2) With domChild2 Debug.Print " Level 2: nodeName(" & intChild2 & ") = " _ & .nodeName Debug.Print " Level 2: text(" & intChild2 & ") = " _ & Left$(.Text, intMaxLength) If .hasChildNodes Then 'Third-level child nodes For intChild3 = 0 To .childNodes.length - 1 If intChild3 > intMaxNodes Then Exit For End If Set domChild3 = .childNodes(intChild3) With domChild3 Debug.Print "Level 3: nodeName(" & intChild3 & ") = " _ & .nodeName Debug.Print "Level 3: text(" & intChild3 & ") = " _ & Left$(.Text, intMaxLength) End With Next intChild3 End If End With Next intChild2 End If End With Next intChild1 End If End With Next intNodes Else 'There's nothing in the node list End If End With End Sub

The hasChildNodes property returns True if the current node contains child nodes, and the length property returns the number of child nodes. The length property is the counterpart of the Count property for collections. The nodeName property value returns the type (class) or element name, and the text property returns the element value. At the lowest level of the hierarchy, nodeNames are data element tag names and text returns the data values.

Following is the abbreviated result of passing the IXMLDOMNodeList object generated by the SQLXML.GetTop10 method to the PrintNodeList procedure:

Level 0: nodeName(0) = xsi:type Level 0: text(0) = sqlresultstream:SqlResultStream Level 1: nodeName(0) = #text Level 1: text(0) = sqlresultstream:SqlResultStream Level 0: nodeName(1) = sqlresultstream:SqlXml Level 0: text(1) = AMPL0692AmplifiersSonyTA-N9000ES... Level 1: nodeName(0) = SqlXml Level 1: text(0) = AMPL0692AmplifiersSonyTA-N9000... Level 2: nodeName(0) = row Level 2: text(0) = AMPL0692AmplifiersSonyTA-N9000ES... Level 3: nodeName(0) = SKU Level 3: text(0) = AMPL0692 Level 3: nodeName(1) = Category Level 3: text(1) = Amplifiers Level 3: nodeName(2) = Brand Level 3: text(2) = Sony Level 3: nodeName(3) = Model Level 3: text(3) = TA-N9000ES Level 3: nodeName(4) = Description Level 3: text(4) = Five-channel Amplifier (ES) Level 3: nodeName(5) = NetPrice Level 3: text(5) = 1000 Level 3: nodeName(6) = Quantity Level 3: text(6) = 503 Level 2: nodeName(1) = row Level 2: text(1) = AMPL0697AmplifiersNilesSI-1230... Level 3: nodeName(0) = SKU Level 3: text(0) = AMPL0697 Level 3: nodeName(1) = Category Level 3: text(1) = Amplifiers Level 3: nodeName(2) = Brand Level 3: text(2) = Niles Level 3: nodeName(3) = Model Level 3: text(3) = SI-1230 FG00737 Level 3: nodeName(4) = Description Level 3: text(4) = Multi-room 12 x 30-Watt Amplifier Level 3: nodeName(5) = NetPrice Level 3: text(5) = 750 Level 3: nodeName(6) = Quantity Level 3: text(6) = 511 'Remaining row nodes omitted for brevity Level 0: nodeName(2) = sqlresultstream:SqlResultCode Level 0: text(2) = 0 Level 1: nodeName(0) = #text Level 1: text(0) = 0

The preceding sample output demonstrates that the data you need to populate a list box or table is contained in the third-level child nodes.

Populating a List Box with XML Data

Tip

If you can't connect to the public Alpha Web service at http://www.oakleaf.ws/sqlxml/alpha[?wsdl], the "Creating and Consuming a Local Alpha Web Service" section, near the end of the chapter, shows you how to provide the service from your computer.

The following event handler generates the SOAP request message for the GetTop10 Web method:

Private Sub cmdGetTop10_Click() 'Event handler for Alpha SQLXML 3.0 stored procedure XML Web services Me.Caption = "Alpha Electronics, Inc. - Top 10 Amplifiers by Net Price" Dim wsAlpha As New clsws_Alpha Set objResponse = wsAlpha.wsm_GetTop10("AMPL", "XXXX", _ "XXXX", "XXXX", "XXXX", "XXXX") Call PrintNodeList(objResponse) Call IterateNodes End Sub

The other three event handlers replace XXXX with valid category codes, such as DVDP (DVD players), TVRC (TV receivers), and CDPL (CD players). The event-handler invokes the PrintNodeList procedure, which is contained in the modNodeList module.

Listing 31.2 demonstrates a simple approach to iterating the <row> nodes and adding data items to a list box. In this case, you add a list box item with a semicolon-separated string that contains multiple child element data (text) values. This approach for filling multicolumn list boxes requires you to replace commas and semicolons in the returned values with another character, such as a hyphen.

Listing 31.2 The IterateNodes subprocedure tests the validity of the SOAP response message and adds an item to the list box for each <row> element.

Private Sub IterateNodes() 'Iterate nodes and display results in a list box Dim nodXml As MSXML2.IXMLDOMNode Dim nodRow As MSXML2.IXMLDOMNode Dim intRows As Integer Dim intRow As Integer Dim intCol As Integer Dim strItem As String Dim strTemp As String intRows = lstData.ListCount - 1 On Error Resume Next For intRow = 0 To intRows 'Clear the list box lstData.RemoveItem (0) Next intRow On Error GoTo 0 With objResponse If .length = 3 Then 'Test sqlResultStream:SqlResultCode If .Item(2).Text = "0" Then 'sqlResultStream:SqlXml is second item Set nodXml = objResponse.Item(1).childNodes(0) Else MsgBox "SQL Server returned an error.", _ vbOKOnly + vbExclamation, "Request Failed" Exit Sub End If Else MsgBox "Incorrect node list length (" & _ .length & "); should be 3.", _ vbOKOnly + vbExclamation, "Request Failed" Exit Sub End If End With With nodXml 'Iterate by row For intRow = 0 To nodXml.childNodes.length - 1 Set nodRow = nodXml.childNodes.Item(intRow) strItem = "" With nodRow 'Iterate by column to generate the list entry 'with semicolon-separated values For intCol = 0 To nodRow.childNodes.length - 1 'Replace , and ; with - strTemp = Replace(nodRow.childNodes.Item(intCol).Text, ",", "-") strTemp = Replace(strTemp, ";", "-") If intCol = 5 Then 'Add currency format strTemp = Format$(CSng(strTemp), "$0.00") End If strItem = strItem + strTemp + ";" Next intCol End With strItem = Left$(strItem, Len(strItem) - 1) 'Add the row to the Access list box lstData.AddItem strItem Next intRow End With End Sub

TheSet nodXml = objResponse.Item(1).childNodes(0) statement retrieves the SqlXml node. SqlXml's child nodes are <row> elements assigned to the nodRow variable, which represents a list box item, in the first For...Next loop. The second For...Next loop generates the semicolon-separated item string.

Filling a Table with a Complex SOAP Response Message

Populating local Jet or SQL Server tables with data from Internet-accessible Web services lets you update remote Access applications with new reference information. As an example, you can provide customers, sales agents, distributors, and others in the supply chain with up-to-date information on new or discontinued products, price changes, inventory levels, and related product information. The process is similar to one-way Access briefcase replication or SQL Server merge replication. Many firewalls block binary replication traffic; using a Web service makes the updates available to anyone with an Internet connection. Usernames and passwords in the SOAP request message can authenticate service consumers. HTTPS provides security by encrypting the SOAP request and response messages between endpoints.

Code to populate a table from an IXMLDomNodeList object is similar to that of the preceding section's IterateNodes subprocedure. Listing 31.3 is the VBA code to add records to the tblTopNProducts table. Code for adding rows to or updating the table is simpler than that for adding items to a list box.

Listing 31.3 The PopulateTable subprocedure is a simplified version of AlphaTest.mdb's IterateNodes code.

Private Sub PopulateSubform(objResponse As _ MSXML2.IXMLDOMNodeList) 'Iterate nodes and display results in a subform Dim nodXml As MSXML2.IXMLDOMNode Dim nodRow As MSXML2.IXMLDOMNode Dim intRows As Integer Dim intRow As Integer Dim intCol As Integer Dim strData As String With objResponse If .length = 3 Then 'Test sqlResultStream:SqlResultCode If .Item(2).Text = "0" Then 'sqlResultStream:SqlXml is second item Set nodXml = objResponse.Item(1).childNodes(0) Else DoCmd.Hourglass False MsgBox "SQL Server returned an error.", vbOKOnly + vbExclamation, "Request Failed" Exit Sub End If Else DoCmd.Hourglass False MsgBox "Incorrect node list length (" & .length & _ "); should be 3.", _ vbOKOnly + vbExclamation, "Request Failed" Exit Sub End If End With Me.sbfTopN.Form.AllowAdditions = True With Me.sbfTopN.Form.Recordset 'Iterate by row For intRow = 0 To nodXml.childNodes.length - 1 'New row .AddNew Set nodRow = nodXml.childNodes.Item(intRow) 'Add data to table by column For intCol = 0 To nodRow.childNodes.length - 1 strData = nodRow.childNodes.Item(intCol).Text .Fields(intCol).Value = strData Next intCol .Update Next intRow End With Me.sbfTopN.Form.AllowAdditions = False End Sub

The table defines the data type for each column, so you don't need to use the CCur or CInt functions to change the String datatype to Currency or Integer for the NetPrice and Inventory fields. VBA handles the Variant type change, which developers call Evil Type Coercion (ETC), automatically.