This tutorial assumes that you already generated and compiled your Microsoft Office Lists web service and web site using the Developing Microsoft Office Synchronizable Lists tutorial.
This tutorial will now guide you through deploying and configuring your web service so that Access 2007+ can consume it.
Configuring IIS
Create an application pool
We're going to create an application pool which will contain our application.
Open IIS and create a new application pool named SamplePool which:
1. runs under the user account which generated the database (it should be yours),
 |
Note: By using the account used to generate the database, we assure ourselves to have all required permissions in the persistence layer. If using another account, make sure to set all required permissions. |
2. in IIS 7.0: use the Classic pipeline mode.
|
Note: the default CodeFluent web.config template is made to work with IIS 6 and 7. However, in IIS 7 the generated configuration is Classic pipeline mode compliant, but ab error will be raised if running in another mode. Two options are actually available: switching your mode as we did, or update your configuration. |
Select the Sample.Office application and add it to the SamplePool application pool.
Adding a generic script map
Even though our web service and web site are functional, we have to add a filter that will allow our web service to be consumed by Office clients. Since Office clients are made to synchronize with a SharePoint site, they're issuing specific requests to which they expect a very specific response. To emulate a SharePoint server, we developed an assembly named CodeFluent.Runtime.Isapi.dll which acts as a translator between the Office clients and the Office Lists web service.
What we need to do, is configure IIS so it calls the CodeFluent.Runtime.Isapi assembly before our web service, so the runtime can alter the request as needed. To do so, IIS provides a simple yet powerful feature which matches exactly our need: the Wildcard Script Map feature.
Select the Sample.Office application and set a generic script map which filters all requests (*) using the CodeFluent.Runtime.Isapi assembly.
|
Note: You must use CodeFluent.Runtime.Isapi which matches the application pool platform. For instance, in IIS 7.0 on a 64bit server, 32bit application support is disabled by default. In those cases you must either use the 64bit version of the CodeFluent.Runtime.Isapi; or set the application pool to enable 32bit application support. |
Now our virtual application can converse with Access, however Access cannot converse with our web service yet.
When connecting to a SharePoint site, Access issues a request to the web site root, expecting the web site to redirect it. In order to support this scenario, we need to set the same generic script map that we set on the Sample.Office application on the IIS web site containing the application.
In IIS, select the Default Web Site and set a generic script map filtering all requests (*) using the CodeFluent.Runtime.Isapi assembly.
Add the Default Web Site to the SamplePool application pool so the web site is ran using the right account and pipeline mode.
Now, our web service can communicate with Access, it's in the right pipeline mode, and uses a user account with permissions to read and write to the database: let's use it in Access!
Using Generated Office Lists in Access
Open Microsoft Access 2007 or upper:
1. select the Create tab,
2. click on the SharePoint Lists menu,
3. click on Existing SharePoint List,
4. A new window will pop-up. As the SharePoint site address specify the folder containing the lists.asmx web service: http://localhost/Sample.Office/en-US
5. Keep the Link to the data source by creating a linked table. option checked,
6. Click Next,
7. Select the Customer - LoadAll list, and click OK.
The Customer - LoadAll table will appear in the left task pane. By double clicking on it you'll get a table view where you can edit it's content.
It's also possible to click on the Form button in the Create tab in order to get a form view of the table content.
Troubleshooting Errors
Microsoft Excel and Access catch all back-end errors and hide them from the user, making it hard to debug for a developer. To troubleshoot a server-side problem, a good practice is to use an internet browser and call the web service (lists.asmx) directly by using the corresponding methods, this way you'll be able to see what's going on. Moreover it's good to know, that the CodeFluent.Runtime.Isapi assembly logs its server-side errors in the Windows event log. This way another good practice is to check the event log for errors on unexpected behaviors.
Another point of interest is that all requests are HTTP requests, meaning that using tools like Fiddler, you'll be able to see issued requests and reproduce them using a browser.
Finally, when absolutely nothing happens or works as expected, its often a security or platform (32bit/64bit) issue.