9.6 Using Web Services in Access

While web services aren't likely to factor into the tables at the heart of an Access database, they can be very useful in forms and reports. To demonstrate, the following example will use the Zip Code web service shown earlier as a way of filling in an address form without the user having to type in the city or state.

To get started, create a database, and then fire up the Microsoft Office Web Services Toolkit. The steps for generating code to work with a web service in Access are precisely the same as they were in Excel, so you can open the Visual Basic Editor and follow the same steps to create a web service wrapper associated with the WSDL file http://webservicex.net/uszip.asmx?WSDL. Once you have created that wrapper, make a table containing basic address information, like the address table shown in Figure 9-19.

Figure 9-19. The address table that forms the base of the example

A basic form, created using the Form Wizard's "columnar" option, provides users (and the web service) with access to the information in the table. The design of the form is shown in Figure 9-20.

Figure 9-20. The form that will host the web service, shown just before the After Update event is triggered

For my own convenience, I've set the Auto Tab property of the City, State, and AreaCode fields to "No," leaving them accessible if I need to change them but keeping them out of the way because that information should fill automatically once a Zip Code is entered into the ZIPCode field. (To set this property, right click on the field and select Properties. You can find Auto Tab under either the Other tab or the All tab.) The crucial modification this form needs, however, is adding the code shown in Example 9-5.

Example 9-5. AfterUpdate code for updating fields when a Zip Code is entered

Private Sub ZIPCode_AfterUpdate( )      Dim zipResolver As clsws_USZip Set zipResolver = New clsws_USZip      Dim returnedNodes As MSXML2.IXMLDOMNodeList      'Send the web service the text value of the ZIPCode field Set returnedNodes = zipResolver.wsm_GetInfoByZIP(Me.ZIPCode.Text)      'Put the results in the City, State, and AreaCode fields Me.City = returnedNodes.Item(0).selectSingleNode("//CITY").Text Me.State = returnedNodes.Item(0).selectSingleNode("//STATE").Text Me.AreaCode = returnedNodes.Item(0).selectSingleNode("//AREA_CODE").Text      End Sub

To add the code, right-click on the ZIPCode field and select Properties . . . . In the Event tab, click in the field to the right of After Update, and then click on the ellipsis button to the right of that. Select Code Builder from the dialog box, and enter the code shown in Example 9-5. Close the Visual Basic Editor, and switch the form from Design View to Form View. As you enter values and reach the Zip Code value, the form should look like Figure 9-21.

Figure 9-21. The data just after the Zip Code's After Update event is triggered

Once you tab to the next field, the VBA code will call the web service and enter the values it retrieves into the City, State, and Area Code fields, as shown in Figure 9-22.

Figure 9-22. A form letter with fields

Every time the user makes a change to the Zip Code and leaves the field, the City, State, and Area Code fields will update accordingly. The user can still make changes to those fields after the update, and those changes will remain provided that there are no further changes to the Zip Code.

You can also use the REST version of this service in Access by substituting the code shown in Example 9-6 for the code in Example 9-5. If you use this, there's no need to use the Web Services Toolkit at all.

Example 9-6. REST version of Access web services call

Private Sub ZIPCode_AfterUpdate( )      Dim query As String      'assemble query string query = "http://webservicex.net/uszip.asmx/GetInfoByZIP?USZip=" + _    Me.ZIPCode.Text      'define XML and HTTP components Dim zipResult As New MSXML2.DOMDocument Dim zipService As New MSXML2.XMLHTTP      'create HTTP request to query URL - make sure to have 'that last "False" there for synchronous operation zipService.Open "GET", query, False      'send HTTP request zipService.send      'parse result zipResult.LoadXml (zipService.responseText)      Me.City = zipResult.selectSingleNode("//CITY").Text Me.State = zipResult.selectSingleNode("//STATE").Text Me.AreaCode = zipResult.selectSingleNode("//AREA_CODE").Text      End Sub

The REST code produces exactly the same behavior shown in Figures Figure 9-21 and Figure 9-22. The core logic of this example is the same as it was in Example 9-4, just as Example 9-5 echoes Example 9-2. The only difference between using web services in Excel and using them in Access is the objects provided by the application context. This book can't begin to teach you everything about the object models in these applications, but once you learn those, the web services integration is simple.