Set up and configure Access Services (SharePoint Server 2010)

Published: May 12, 2010

Access Services in Microsoft SharePoint Server 2010 is a service that allows users to publish an Microsoft Access 2010 Web database to a SharePoint site. When an Access Web database is published to SharePoint Server 2010, a site is created to host the Web database, plus it moves all of the database objects and data to a SharePoint list on that site.

While you do not need the Access client to use the published Web database, the Access client is required to make any changes to the database structure. In addition, a user account is required to use the Web database on SharePoint Server 2010. Anonymous access is not supported.

Deploy Access Services

The following steps are needed to deploy Access Services:

Install and configure Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 (SSRS).
Create an account in the Active Directory directory service to run the application pool for the Access Services service application.
Register that account as a managed account in SharePoint Server 2010.
Start Access Services.
Create an Access Services service application.

To create an Access Services service application

On the Central Administration home page, under Application Management, click Manage service applications.
On the Manage Service Applications page, click New, and then click Access Services.
In the Access Services Application Name section, type Access Services in the text box.
Select the Create new application pool option and type AccessServicesAppPool in the Application pool name text box.
Select the Configurable option, and from the drop-down list, select CONTOSO\accessapppool.
Click OK.

Access Services settings

There are no additional settings required after the initial installation of Access Services. The services are ready to use as soon as the SharePoint Server 2010 installation is completed. However you may change the default configuration settings to suit your business need. The following table contains the configurable Access Services settings.

To configure an Access Services service application

Verify that the user account that is performing this procedure is a site administrator for the Access Services service application and also has Designer permissions.
On the Home page of the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
Click the Access Services service application that you want to change.

On the Access Services Settings page, change any of the following settings:

Setting	Description
Lists and Queries	Settings for queries used against Microsoft SharePoint Foundation lists
Maximum Columns per query	The maximum number of columns that can be referenced in a query. Note that some columns may automatically be reference by the query engine and will be included in this limit. Valid values: from 1 to 255 Default value: 32
Maximum Rows per query	The maximum number of rows that a list used in a query can have, or that the output of the query can have. Valid values: from 1 to 200000 Default value: 50000
Maximum Sources per query	The maximum number of lists that may be used as input to one query. Valid values: from 1 to 20 Default value: 8
Maximum Calculated Columns per query	The maximum number of inline calculated columns that can be included in a query, either in the query itself or in any sub-query on which it is based. Calculated columns in the underlying Microsoft SharePoint Foundation list are not included. Valid values: from 0 to 32 Default value: 10
Maximum Order by Clauses per query	The maximum number of Order By clauses in the query. Valid values: from 1 to 8 Default value: 4
Allow Outer Joins	Allow left and right outer joins in a query. Inner Joins are always allowed. Check box: selected or cleared for Outer Joins allowed.
Allow Non Remote-able Queries	Allow queries that cannot be remoted to the database tier to run. Check box: selected or cleared for Remotable Queries allowed.
Maximum Records Per Table	The maximum number of records that a table in an application can contain. Valid values: -1 (indicates no limit), any positive integer Default value: 500000
Application Objects	Limitations on the types of objects an Access Services application can contain
Maximum Application Log Size	The maximum number of records for an Access Services Application Log list. Valid values: -1 (indicates no limit), from 1 to any positive integer Default value: 3000
Session Management	Behavior of Access Database Service sessions
Maximum Request Duration	The maximum duration (in seconds) allowed for a request from an application. Valid values: -1 (indicates no limit), 1 through 2007360 (24 days) Default value: 30
Maximum Sessions Per User	The maximum number of sessions allowed per anonymous user. If a user has this many sessions and starts a new session, the user’s oldest session is deleted. Valid values: -1 (no limit), from 1 to any positive integer Default value: 10
Maximum Sessions Per Anonymous User	The maximum number of sessions allowed per user. If a user has this many sessions and starts a new session, the user’s oldest session is deleted. Valid values: -1 (no limit), from 1 to any positive integer Default value: 25
Cache Timeout	The maximum time (in seconds) that a data cache can remain available, as measured from the end of each request for data in that cache. Valid values: -1 (indicates no limit), 1 through 2007360 (24 days) Default value: 300
Maximum Session Memory	The maximum amount of memory (in MB) that a single session can use. Valid values: 0 (disable) through 4095. Default value: 64
Memory Utilization	Allocation of memory on Access Database Service
Maximum Private Bytes	The maximum number of private bytes (in MB) allocated by the Access Database Service process. Valid values: -1 (the limit is set to 50% of physical memory on the computer), any positive integer Default value: -1
Templates	Settings related to template management
Maximum Template Size	The maximum size (in MB) allowed for Access Templates (ACCDT). Valid values: -1 (no limit), from 1 to any positive integer Default value: 30

Click OK.

Change History

Date	Description	Reason
May 12, 2010	Initial publication