Overview of SharePoint Access Services

If you have deployed SharePoint Server 2010/2013 with the Enterprise license to your organization, then you can purchase the Office Web Apps (OWA) add-on to provide your SharePoint portal users with browser-based editions of Word, Excel, PowerPoint, and OneNote. IN case you wondered, “But what about Access?” doesn’t fret? Access Services is included with the SharePoint 2010 Enterprise license. The coolest thing about Access Services and OWA is that you can host Microsoft Office documents in your SharePoint portal and your users don’t have to have the full-blown Office applications installed on their computers; the only requirements on the client side are (a) a Web browser; and (b) access to the SharePoint portal.

By the end of this brief article you will understand how to publish Access 2010/2013  databases to your SharePoint portal with a minimum of muss, fuss, or greasy aftertaste.

Set up and configure Access Services 

Access Services in Microsoft SharePoint Server 2010/2013 is a service that allows users to publish a Microsoft Access 2010 Web database to a SharePoint site. When an Access Web database is published to SharePoint Server 2010/2013, 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/2013. Anonymous access is not supported

Deploy Access Services

The following steps are needed to deploy Access Services:

1.      Install and configure Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 (SSRS).

2.      Create an account in the Active Directory service to run the application pool for the Access Services service application.

3.      Register that (Polasoft\accessapppool) account as a managed account in SharePoint Server 2010.

4.      Start Access Services.

5.      Create an Access Services service application

To create an Access Services service application

1. On the Central Administration home page, under Application Management, click Manage service applications.

2. On the Manage Service Applications page, click New, and then click Access Services.

3. In the Access Services Application Name section, type Access Services in the text box.

4. Select the Create new application pool option and type AccessServicesAppPool in the Application pool name text box.

5. Select the Configurable option, and from the drop-down list, select Polasoft\accessapppool.

6. Click OK.

On the Access Services 2010/2013 settings page, change any of the settings shown in the following table.

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: 40
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: 25000
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: 12
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 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 0 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: 1500
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

Enabling Session State for SharePoint 2010 Access Reports:

1) Open SharePoint 2010 Management Shell

2) Type in the following command PS C:\Users\shiva> enable-SPSessionStateService

3) You should get the following response:-

cmdlet Enable-SPSessionStateService at command pipeline position 1
Supply values for the following parameters:

4) Type in the Database Name as accessreports (DatabaseName: accessreports) – This will create a new database in SQL names accessreports

5) You will be return to the root path PS C:\Users\shiva>

6) Open the normal command prompt and restart the IIS by typing in iisreset

Access Services lets you quickly create web applications and make them available to your SharePoint users via their web browser. The great thing is you don’t actually need to know much about SharePoint or be a web developer to make your application, the only thing you need is Microsoft Access skills.

The process for creating an Access Services database is:

1.      Create a database with tables and forms in Access 2010.

2.      Click the Home menu and choose the Save and Share option.

3.      Perform the compatibility test and fix any issues.

4.      Publish to SharePoint – enter a site name (Access Services creates a site for each published Access database).

Access Services migrates the data from the Access MDB file into the SharePoint Content database (as lists). This allows multi-user access to the Access Services database. One thing to note is that Access Services databases can’t use a SQL Server backend database.

SharePoint Access Services 2010/2013 – Benefits:

§  Access Services are nothing but Data driven web applications called web databases hosted in the SharePoint 2010/2013 environment

§  Access Services Benefits and Usage

§  Balance between business agility and IT manageability

§  No Install Solution –  Web based Access

§  Improved Collaboration – Share and collaborate on the declarative RAD no-code web based team databases.

§  Centralized Data Storage – Single truth of the Application Logic and Data

§  Improved Reliability, Scalability, Security, and Manageability – Central IT management

§  Improved Backup/Restore – Access databases are part of the SharePoint Backup and Restore Process

§  Increased Concurrency – Locks the database at the object level, instead of database file level resulting in fewer conflicts

§  Access Applications Standardization using Templates – IT can configure/support services, start building out the web database standards, and let end-users manage it

§  RAD No-Code SharePoint Applications – Useful to build web based powerful RAD applications which supports query engine that can perform complex joins, filtering, aggregations, and parent-child relationships between lists.

§  RAD Reporting Tool – Useful as reporting tool to generate the customized reports (RDL files) based on SharePoint Lists hosted in the SharePoint.

Architecture Details:

§  Access Service is a middle-tier service, which handles the query processor and data access layer. It also manages communication between Access Web Application and SharePoint Content Databases.

§  Support for the Relational Database Data Integrity using SharePoint Lists Enhancements Infrastructure – Lists Relationships, Cascade Deletes, Unique Constraints, and Data level validations at the List and Item Level

§  Improved Performance/Scalability – Offers caching layer that addresses the limitations of the maximum number of list items that a query can return at one time by ignoring the List View Threshold. Improved Performance by allowing large record sets in the SharePoint List. Access DB supports more than 100K records. SharePoint Lists has filtering or sorting restrictions if you have more than 5K records in the list because content database blocks large calls. Access Services Data Sheets retrieves all the 100K SharePoint list items in the 2K records in chunk and later stitches them together in the ADO.NET record set in memory Layer as cached data for the performance. This allows sorting and filtering against cache instead of content database on the more than 50K records in the Access datasheet. Access datasheet View is rendered by the Project Data grid object and it is smart enough to bring only 200 records at a time in the browser. With the smart navigation using AJAX experience, as you scroll down, it will bring additional records from the cache without affecting user experience. Access List Views are continuous forms supports paging, sorting, and filtering, and behaves same way as Datasheet View by retrieving data from the ADO.NET cache in the middle tier.

§  Concurrency Conflicts – Different users can make changes to different objects or different data items in a list without causing conflicts. When data conflicts do occur, a conflict resolution wizard enables the user to choose which version of data items to preserve. For object conflicts, Access provides the name of the user who made the saved changes and creates a renamed backup copy of the local object before downloading the other user’s changes.

§  Source Control – While downloading the database locally on file system, Access client downloads the entire database only when a user doesn’t have local copy. Access fetches only objects or data items that have changed.

§  Only Web databases are supported in the Access Services. Web database supports two types of Access Objects – Web Objects that can run either in browser or Access Client and client objects (non-web objects) that can only run in the Access client. All design changes for both web and client objects must be made in Access Client. It is important to remember that client object definitions are published and stored in the SharePoint but they can accessed and executed during runtime only in the Access client.

§  All linked tables are client tables. Only client objects like reports, forms, and macros can work with linked tables. Linked tables aren’t available to the web objects.

§  All the reports, forms, and macros with VBA code make these objects client objects.

§  All the web objects like reports, forms, and macros would work only with the web tables (Note web tables are Access DB tables fully compatible with SharePoint lists)

§  When working in the Access client and connected to the network where Access Service App is running, data and design changes to web tables automatically synchronize with the server.  When disconnected, Access client works with local copy and doesn’t allow changes to the tables. When reconnected, Access notifies users to synchronize and resolve any conflicts. Design changes to objects other than web tables synchronize only when users explicitly request sync by clicking Sync All button.

§  Forms and Reports gets rendered in the Data Form Web Part

§  Data Sheets rendered in the Project Data grid JS Object to support large data sets

§  Reports are rendered in the Report Viewer Web Part installed with the SSRS Add-in

§  Access Service AJAX WS provides AJAX experience on the Access Web Applications

§  Better Administrative Control – Uses the OOB SharePoint Security for the Security Layer 

§  Managing and Fine Tuning the Access Services – From the Central Admin -> Manage Access Services Settings e.g. Max columns/rows per query, Max sources per query, Max calculated columns per query, Max order clauses per query, Max records per table in the join, Max sessions per users etc.

Why use Access Services:

Access Services applications can be built by anyone with Microsoft Access with a very low learning curve. No need to learn .NET development.

Existing Access Databases can be converted. They must be upgraded to Access 2010 /2103 first and a few changes may be required to meet the deployment requirements, but will often be far quicker than developing a new application.

Performance is great for remote users. The application is accessed via your web browser and is a great way to improve performance for remote users.

Your database becomes multi-user with minimal effort!

Because it’s SharePoint your application is available on the intranet without additional infrastructure and licensing.

A few other things you should know:

•User access management is handled by SharePoint security.

•Locks the database at object level rather than file level. Better concurrent user access.

•Allows large lists (100,000 items is easily supported)

•Master page branding is not supported. Uses Access branding.

•Linked tables are not supported

•Access Services sites can’t be edited using SharePoint designer

Requirements:

To use Access Services you need the following:

•SharePoint 2010/2013 Enterprise

•SQL 2008 R2 with Reporting Services installed in SharePoint Integrated mode

At this point you have the fundamentals under your belt such that you should be able to verify the running state of Access Services in SharePoint 2010 and publish an Access 2010 database to your portal. Please be sure to leave your questions and/or observations in the comments area of this post; I’m happy to help

Hope this helps someone… J