Access Cookbook - Ken Getz [323]
Locating the toolkits
The Microsoft Office 2003 Web Services Toolkit can be found at http://www.microsoft.com/downloads/details.aspx?FamilyID=fa36018a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en.
The Microsoft Office XP Web Services Toolkit 2.0 can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxpwst2/html/odc_offxpwstoolkit2.asp.
The Microsoft SOAP Toolkit 3.0 can be found at http://msdn.microsoft.com/library/default.asp?URL=/downloads/list/websrv.asp.
See Also
Integrating XML Web Services Into Microsoft Office Solutions (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office09062001.asp).
17.5. Work with a Web Service that Returns a DataSet
Problem
Some web services return complex objects that are not readily understood by Access. For example, you might wish to call a web service that returns a .NET DataSet. Is it possible to call such a web service from Access?
Solution
Web services communicate using the text-based protocols HTTP and SOAP. Thus, any complex objects must be converted from the normal binary format into text. This process is known as serialization. .NET automatically serializes many of its built-in objects, including the DataSet, into XML. Thus, a .NET-based web service that returns a DataSet, in reality returns an XML document that represents the DataSet.
When possible, the Microsoft Office 2003 Web Services Toolkit maps complex object return values into compatible types. The serialized XML representation of a DataSet returned by a web service is mapped by the toolkit into an MSXML2.IXMLDOMNodeList object. This object is part of the MSXML component that you can use to navigate through XML documents from Access.
The RunningCalculator web service introduced in the Solution in Recipe 17.4 contains the GetMileSplits method which returns a DataSet filled with mile splits for a given distance and total time. Follow these steps to create an Access form that calls the GetMileSplits method, navigates through the XML returned by the web service, and populates an unbound listbox control on the form with the mile splits:
If you haven't yet done so, download and install the Microsoft Office 2003 Web Services Toolkit.
Start Access 2003 and create an unbound form named frmSplitCalculator.
Add the controls listed in Table 17-2 to the form. Size the controls to your liking.
Table 17-2. Controls for frmSplitCalculator
Control
Name
TextBox
txtDistance
TextBox
txtHours
TextBox
txtMinutes
TextBox
txtSeconds
CommandButton
cmdCalculateSplits
ListBox
lstSplits
From the VBA editor, select Tools → Web Service → References.... This menu item is added to the VBA editor by the Microsoft Office 2003 Web Services Toolkit.
TIP
If you've already completed the Solution in Recipe 17.4 and are working within the same database, you can skip Steps 4-7.
At the Microsoft Office 2003 Web Services Toolkit dialog box, select the Web Service URL radio button and enter the following URL into the URL textbox:
www.deeptraining.com/webservices/runnercalculator.asmx
The RunnerCalculator service and its methods should be displayed in the SearchResults box. Check the checkbox to the left of RunnerCalculator and click the Add button at the bottom of the dialog box to add a reference to the RunnerCalculator service.
The toolkit adds a new class module to the project with the name clsws_RunnerCalculator. This class serves as a proxy for making calls to the web service. The code in this class will take care of speaking to the web service using the SOAP protocol.
Attach the following code to the cmdCalculateSplits button's Click event to use the clsws_RunnerCalculator proxy class to call the GetMileSplits method:
Private