Access Services Overview

Access Services is designed to allow connectivity between SharePoint and Access 2010 databases for business applications where out-of-the-box lists and libraries may not fully meet the business needs. Several site templates are available that can be used to create sites with specific purposes: Assets Web Database, Charitable Contributions Web Database, Contacts Web Database, Issues Web Database, and Projects Web Database. Skilled Access DBAs can also create tables, forms, queries, and reports and then publish them to the SharePoint 2010 environment. This section gives an example of creating a Projects Web Database from a template to give an idea of the out-of-the-box templates that are available, but won’t cover a new build in Access.

Access Services is also only available with SharePoint Server 2010 Enterprise, and if the wizard is used to configure the farm, and Access Services is left checked, an Access Services service application should already be present in the farm. If a new service application is needed, it can be created from the Manage Service Applications page on the Central Administration site. The details of each configuration will vary based on the needs of the organization, and the settings for the service application are discussed here from a high level.

Compared to an Excel Services service application, the Access Service service application is relatively simple, and only includes one page of settings, as follows:

• Lists and Queries— Maximum Columns per Query (limit 255), Maximum Rows per Query (limit 200,000), Maximum Sources per Query (limit 20), Maximum Calculated Columns per Query (limit 32), Maximum Order by Clauses per Query (limit 8), Allow Outer Joins (yes/no), Allow Non Remotable Queries (yes/no), Maximum Records per Table (no limit)

• Application Objects— Max Application Log Size (no limit)

• Session Management— Maximum Request Duration (limit 2073600 seconds [24 days]), Maximum Sessions per User (no limit), Maximum Sessions per Anonymous User (no limit), Cache Timeout (limit 2073600 seconds [24 days]), Maximum Session Memory (maximum 4095MB)

• Memory Utilization— Maximum Private Bytes (limit of 50 percent of the physical memory on the machine)

• Templates— Maximum Template Size (no limit)

One prerequisite is important if the reporting features of these sites are going to be used (which users will almost certainly request if they are given one of these sites): The installation of Microsoft SQL Server 2008 R2 Reporting Services add-in for SharePoint Technologies 2010 (SSRS) is required. Additional instructions are available from Technet (http://technet.microsoft.com/en-us/library/ee662542.aspx) and should be reviewed because this can be a nontrivial installation. It should be noted that there are two modes in running Reporting Services with SharePoint Server: connected mode and local mode. Connected mode requires SharePoint Server 2010, the SSRS add-in, and a SQL Server 2008 R2 Report Server. Local mode only requires SharePoint Server and the SSRS add-in.