How to implement SharePoint 2010 Access Services

Introduction

BI Services Series

How to implement SharePoint 2010 Access Services

How to setup Secure Store and BCS to access LOB data

How to enable document conversion from Word to HTML in SharePoint 2010

How to set up Excel Services to use Secure Store Services and an unattended service account

This posting walks through the process of implementing SharePoint 2010 Access Services, step-by-step.  Implementing Access services requires that the administrator work with several different technologies, including Silverlight, Session State, SharePoint Services and Service Applications, and Microsoft Office products.  Implementing Access services will involve several key steps:

1. Install Silverlight: Microsoft Silverlight us used to generate many of the Access Online user interfaces, such as New and Edit forms.
2. Enable ASP.NET Session State Service: SharePoint Server 2010 uses ASP.NET session state to persist user data between successive requests.  It is needed when working with online implementations of Microsoft Access, Excel and other office tools.
3. Configure Access services: configure service applications.

This posting was developed on the small farm, two-tier SharePoint Server 2010 environment developed in a previous posting.

Step 1: Install Silverlight on clients

• Install Microsoft Silverlight on all client machines that will be connecting to the online Access database application.

Step 2: Enable ASP.NET Session State Service

1. In Central Administration, go: Application Management > Service Applications > Manage service applications.
2. Verify that the SharePoint Server ASP.NET Session State Service is not listed.
3. On the SharePoint Server 2010 host machine, open the SharePoint 2010 Management Shell.
4. Execute the following command:
• Enable-SPSessionStateService –DefaultProvision
  
5. In Windows Explorer, navigate to the Web.Config file of the web application that will be used to host Access webs:
• C:\Inetpub\wwwroot\wss\VirtualDirectories\
  
6. Edit the Web.Config file by setting enableSessionState to True.
   
7. Save the file.
• Changes to the web application Web.Config file automatically trigger a restart of the application.
8. To manually restart IIS, use iisreset:
   
9. In Central Administration, go: Application Management > Service Applications > Manage service applications.
10. Verify that the SharePoint Server ASP.NET Session State Service is now listed
    
    NOTE: failure to enable ASP.NET Session State Service will not stop Microsoft Access database applications from being successfully published to SharePoint Server 2010.  However, when you attempt to actually connect to the web-enabled version, you may experience the following error:
    
    If this error does occur, check to make sure that the ASP.NET Session State Service has been started.

Step 3: Configure Access Services

1. Install Reporting Services Add-In:
1. Logon to the SharePoint Server 2010 host.
2. Install the SQL Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint 2010
   
2. Provision Service Account:
• Use sp_app domain account.
3. Register service account as a managed account:
1. In Central Administration, go: Security > General Security > Configure managed accounts.
2. Add domain service account to list of managed accounts
   
4. Start Access Services:
1. In Central Administration, go: Application Management > Manage services on server.
2. Look for Access Database Service in the list
   
3. Click Start if the service is not already started.
• In a default small farm two-tier installation, this service will already have been started.
5. Create Access Service Application
1. In Central Administration, go: Application Management > Manage Service Applications.
2. Look for Access Services in the list
   
3. Create a new Access Service Application if one is not already created.
• In a default small farm two-tier installation, this service application will already have been created.

Optional: Publish the Northwind Traders sample Access application

1. Download the Northwind sample template, Northwind.accdt, for Office 2010. 
2. Double-click the template to create the runnable database application, Database.accdb.
3. Change the filename to Northwind.accdb, and the save.  The application will open and prompt you to login
   
4. On the ribbon, click the File tab, select Save and Publish, and then select Publish to Access Services:
   
5. Click the Run Compatibility Checker.
6. View the Compatibility Checker results
   
7. Enter the Server URL and enter a custom name for the new site that will be created for this Access application.
8. Click Publish to Access Services.  Logon when requested.
9. After a minute or two, a success prompt appears
   
10. Click the URL to view the online application.  After a minute, a browser will open to the site
    
11. This completes the test.

Optional: Remove a published Access database application

1. From the Access database view:
1. At the top of the online Access database view, click Options, and then click Settings.
2. In the Settings group of links, click Delete this Site.
2. From the site's Site Settings:
1. In the browser address bar, modify the URL by appending, "_layouts/settings.aspx" to the URL:
• ...[server]/[Access database site]/_layouts/settings.aspx
2. In the Site Actions group, click Delete this site.
3. From the root site:
1. Navigate to the root site.
2. From Site Actions, click Site Settings.
3. In the Site Administration group, click Sites and Workspaces.
4. In the list of Sites, click the "X" associated with the site you want to delete.

Summary

This posting has presented a walkthrough for implementing SharePoint Server 2010 Access Services.  For additional detail on the steps and procedure presented here, see the References below.

References

• Microsoft MSDN Blogs: Using Session State in SharePoint 2010
• MSDN Developer Network: Session State Management in SharePoint
• Microsoft TechNet: Manage the State Service (SharePoint Server 2010)
• MSDN Developer Network: Working with Web.config Files
• MSDN Developer Network: Editing ASP.NET Configuration Files
• MSDN Developer network: Managing Changes to Configuration Settings
• TechNet: Set up and configure Access Services (SharePoint Server 2010)
• Microsoft Download Center: Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies 2010
• Microsoft Office 2010: Build and publish an Access database to SharePoint
• Microsoft Office: Sample Access databases that you can download and adapt
• Microsoft Office: Northwind sales web database
• Microsoft Office: Desktop sales pipeline
• Institute for Informatics Georg-August-Universität Gottingen: The MONDIAL Database
• What About SharePoint: Set up and configure Access Services (SharePoint Server 2010)
• Nick Patel: Access Services in SharePoint 2010 - All You Need to Know
• Microsoft Developer Network: Creating Web Databases with Access 2010 and Access Services

Notes

• If you experience the error,  Specified method is not supported, when attempting to delete the published site, see this posting.