Consume Web Service from Access

December 16, 2005



A few months ago, I posted an article here at DBJ named How to Pass Access Data Across the Web.  One reader expressed concern over using the Microsoft Internet Explorer library because of security issues. While I do not share his fear, I appreciate that others might, so I began to think about how the process could be enhanced by substituting a Web Service for the traditional ASP/Querystring web page approach described in the article.  What follows is the fruit of those labors.



Begin with a Web Service

As usual, there is a download associated with this article, but this time it is multi-dimensional. It contains a Microsoft Access database that should work for you, out of the box, so long as you have Access 2003 installed. If it fails to work for you, then you will need to find and/or install and register the MSSoap30.dll library. We will talk about that later, but it is the only piece a user needs to consume a Web Service from Microsoft Access.

For those who want to get into the guts of this thing, I have included the SQL Script for creating the ErrorLog table and the el_InsertErrorLog stored proc (also shown below) and the Dot Net code I used to create my web service. You can explore my simple service, provided my web server is still up, by following this link ...

http://amazecreations.com/ErrorLog/ErrLogFunctions.asmx


Before we continue, allow me to give credit where it is due. My web service began with an example posted in an MSDN Advanced Basics article by Ken Getz. He wrote a simple web service to return the requestor's IP address. I thought it was so simple and elegant, I used it in my own testing, until I moved on to my LogNewError() method. You may play with either of them through the web service default page shown above.

An entire article could be written about creating the web service, and indeed, many have been, so we will leave the details of that a small mystery for this article and move on to the pertinent part, consuming the web service from Access.

Web Services References Toolkit

To make all this work, you need to download a special toolkit from Microsoft that performs all the hard work of creating the class to invoke your web service. Once created, it is just simple VBA programming, but you need this toolkit to get the hard work out of the way. There are a number of articles at MSDN which served as the basis for my education and whatever you may find deficient in my article will probably be answered here:

  MSDN Article: Call Web Service From Microsoft Access       Download TheToolkit

Once installed, it shows up on the Tools menu while in the VBA code window. Select Web Service References ... from the Tools menu, and you are presented with the following dialog box for finding and processing web services. As you can see below, I elected to provide my specific URL for the web service I wanted to attach, but you could use the interface to search for any web service that is publicly exposed. The search results pane shows the names of all web services found, and the methods they expose. Pressing the Add button kicks off a process that creates all of the VBA code necessary to begin using the service. It's really that simple. Close the dialog, you are done!

SQL Server Piece

Although the Dot Net Web Service code is beyond the scope of this article, the database code is pertinent and should be of interest to you. The database accepting our error log records is a Microsoft SQL Server and it contains a table named ErrorLog and a stored proc, el_InsertErrorLog, for inserting new records. The web service requires values for each parameter of the stored proc and our Access function will have to deliver on all of them for it to work. For the purpose of this article, little code was included to validate the user-supplied parameters but in production that would be a must.

/* Create ErrorLog table                         */
 CREATE TABLE [ErrorLog] (
  [ErrorLogID] [int] IDENTITY (1, 1) NOT NULL ,
  [ClientCode] [nvarchar] (8) NULL ,
  [ErrNumber] [int] NULL ,
  [ErrDescription] [nvarchar] (512) NULL ,
  [ErrDateTime] [datetime] NULL ,
  [ObjectName] [nvarchar] (48) NULL ,
  [FunctionName] [nvarchar] (48) NULL ,
  [LineNumber] [int] NULL ,
  [LoginName] [nvarchar] (24) NULL ,
  CONSTRAINT [PK_ErrorLog] PRIMARY KEY  CLUSTERED 
  ([ErrorLogID])  ON [PRIMARY] 
 ) ON [PRIMARY] 
 GO
 /* Create stored proc to insert new records.
 (New error log records are inserted by means of a
  stored proc that accept all necessary fields.  The
  return value is the IDENTITY value for the record.)
 */
 CREATE PROC el_InsertErrorLog   (
  @ClientCode      nvarchar(8)
 , @ErrNumber       int
 , @ErrDescription  nvarchar(512)
 , @ErrDateTime     datetime
 , @ObjectName      nvarchar(48)
 , @FunctionName    nvarchar(48)
 , @LineNumber      int
 , @LoginName       nvarchar(24) 
 , @Result          int output    )
 AS
 BEGIN
  SET NOCOUNT ON
  INSERT INTO ErrorLog
   (ClientCode,
   ErrNumber, 
   ErrDescription,
   ErrDateTime, 
   ObjectName, 
   FunctionName, 
   LineNumber, 
   LoginName)
  SELECT 
   @ClientCode,
   @ErrNumber, 
   @ErrDescription,
   @ErrDateTime, 
   @ObjectName, 
   @FunctionName, 
   @LineNumber, 
   @LoginName
  SELECT @Result = @@IDENTITY
 END

Access Consumes ErrorLog Web Service

Thanks to Ken Getz code out at MSDN Magazine, we have a simple web service to consume as a test, and to get our feet wet. Notice in the screen shot of my little demo app that the first button, What is my IP?, consumes the GetAddress() method of the web service we have added.

The code for this is quite simple:

   Public Function GetMyIPAddress()
 On Error GoTo err_Handler
 Dim sResult As String
 Dim sMsg As String
 DoCmd.Hourglass True
 Dim WS As clsws_ErrLogFunctions
 Set WS = New clsws_ErrLogFunctions
    
 sResult = WS.wsm_GetAddress
    
 sMsg = "Your IP address is: " & sResult
 MsgBox sMsg, vbInformation, "Finished"
    
   exit_Here:
 DoCmd.Hourglass False
 Exit Function
    
   err_Handler:
 MsgBox Err.Description
 Resume exit_Here
   End Function


Since the complexity of the web service request has been obfuscated in the clsws_ErrLogFunctions class, all we need to do is to instantiate an object and execute the wsm_GetAddress() method of the class.

The code for the LogNewError() method is only marginally more complex, as it requires that parameters be passed. The demo form collects the data, scrubs it for NULLs and executes the VBA function LogMyError() which in turn invokes the web service.

   Public Function LogMyError(ByVal sCode As String, _
    ByVal lErr As Long, _
    ByVal sErr As String, _
    ByVal dDate As Date, _
    ByVal sObj As String, _
    ByVal sFn As String, _
    ByVal lLine As Long, _
    ByVal sLogin As String)
                            
 Dim lResult As Long
 Dim sMsg As String
 On Error GoTo err_Handler
 DoCmd.Hourglass True
    
 ' The web service has already been added by invoking the Web
 ' Service References process. (see Tools menu)  The associated
 ' web service that was encapsulated is found at ...
 '   http://amazecreations.com/ErrorLog/ErrLogFunctions.asmx
 Dim WS As clsws_ErrLogFunctions
 Set WS = New clsws_ErrLogFunctions  
    
 ' If successful, the new ErrorLogID will be returned.
 lResult = WS.wsm_LogNewError(sCode, lErr, sErr, dDate, sObj, sFn, lLine, sLogin)
    
 sMsg = "Successfully logged error with ID=" & lResult
 MsgBox sMsg, vbInformation, "Finished"
    
   exit_Here:
 DoCmd.Hourglass False
 Exit Function
    
   err_Handler:
 MsgBox Err.Description
 Resume exit_Here
   End Function

What could go wrong?

The articles out at MSDN are from the January 2002 issue, which is now 4 years old, and quite frankly, some of the troubleshooting help does not jibe with what I currently experienced while getting this process to work. That being said, the following applies when troubleshooting web service consumption from Microsoft Access:

1.      Not Connected to the Internet
You have to be connected. Enough said.

2.      Internet Traffic is Heavy
Slow or blocked service will, of course, make the existing web service unavailable. This is unfortunate, but unavoidable at times. Try again later.

3.      Web Service not Available
You are depending on someone else providing a service, and sometimes it is simply not available. If you attempt to use my code, which hits my web service, and my service is shut off, the code will break. Again, there is nothing you can do about this, except be aware that the possibility exists for this condition.

4.      Syntax Error Calling Web Service
If you use the toolkit to generate the class, all should work fine ... until, perhaps, something changes in the web service provider. If, for example, the parameter list changes, your call will fail. The only solution is to requery the web service and verify that the method still exists and that your understanding of the input/output parameter list is correct.

5.      ** User's Computer Missing Library !!!     Ahhhh, there's the rub.
As mentioned above, the documentation is outdated. It suggests a possible cause for the code not compiling on a user's machine as a missing Microsoft Soap Type Library, MSSoap1.dll. Having used the toolkit to generate the class, I found that, in order to compile the code, users required the MSSoap30.dll library, not the 1.0 version. Looking for this library on a couple of user's machines, it was found in the Office 11 folder (Office 2003) of the Common | Microsoft Shared directory.
     C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSSoap30.dll

If your users do not have Office 2003, then you will have to deploy this DLL, which I understand is eligible for redistribution. Personally, I have not had to deal with this issue just yet, as all my users have Office 2003. If this is a problem for you, I suggest the following link:

SOAP Toolkit 3.0 Redistributable

» See All Articles by Columnist Danny J. Lesandrini








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers