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 ...
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:
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
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:
Connected to the Internet
Traffic is Heavy
Service not Available
Error Calling Web Service
User's Computer Missing Library !!! Ahhhh, there's
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: