Skip to main content

Access Services in SharePoint 2013

Microsoft Access is a 20 year old product and there have been multiple attempts to integrate Access features in earlier versions of SharePoint. Is it better now?

This post will provide you with insight about changes and capabilities — and maybe you will consider using Access Services to manage structured data when creating your next solution.   Simply put, Simply put, SharePoint 2013’s Access Services allows us to host relational databases within SharePoint. The Access client is still used to create and design tables, views, macros, and more. End-users will use a browser when interacting with the Access App. Access Services is available in Office 365 (depending on your plan) and on-premise (see Resources at the bottom of this post).

App model

"Everything" is an App in SharePoint 2013, and Access Apps are no different. These apps are self-contained pieces of functionality, and are easy to install, use, manage, upgrade, and remove. Users discover and download apps from their company’s App Catalogue or the SharePoint store.

Easy to create an Access App

An Access App can be added to your site in three steps;
  • Add
  • Design (database schema and logic)
  • Publish
To add an Access App, go to Site Action and click "Add an App"

Find and select Access App:
 Access App
Give it a name and click "Create".
The next step is to create and design your database schema: Access start adding tables
The Access Client 2013 is required when designing your Access App.
When you have finished database design, Publish it and the App will be part of your site content: Site Contents In my example, I chose a pre-defined schema (customer) to be part of my Access App. The form (user interface) was automatically generated and the necessary artifacts created in SQL Azure (because I am on Office 365). You also have the option to import data from Access databases, CSV files, Excel, ODBC data and SharePoint List when creating your tables (database schema). When the Access App is added to your site, other users can navigate and manipulate the data using the browser: Access App

SQL

All Access data is now stored in SQL Server, which makes it easier to manage the Access data and scale the environment appropriately. Note that Access services requires SQL Server 2012, and a separate SQL server is recommended to host the Access databases. Do the necessary capacity-, performance- and other planning before you enable Access services in your on-premise SharePoint farm. Take a look at this whitepaper for details and other requirements. When using Office 365, the SQL databases are created in SQL Azure.

Security

When an Access App is created a SQL database is automatically created to store the data. Each Access App will generate a new SQL database, and databases are not shared between Access Apps.  The relationship between the Access App and the SQL database is transparent for the end users, and there is no need for the user to authenticate (again) when using the Access App. Permissions to the Access App are managed by adding people (and/or AD groups) to the SharePoint security groups (e.g. Visitor, Member, and Owner) in the site that hosts the Access App. See this whitepaper for necessary configuration steps on on-premise hosting.

Reporting

Because all data is now stored in SQL Server you can use a tool of your preference to create reports. You are able to connect to the SQL database using ODBC and can take advantage of existing skillsets you might have in e.g. Crystal Reports, Excel, and Power View.

Search

Access Services is leveraging the full-text search capabilities within SQL 2012 and is used when people are searching for information using the Access App. The search service in SharePoint is not aware of the content that is hosted within Access services, so consider creating a content source to crawl, process and add Access content to the search index.

Resources

White paper – Office 2013 – Access Services Setup for an On-Premises installation: http://www.microsoft.com/en-us/download/details.aspx?id=30445 Microsoft Access blog: http://blogs.office.com/b/microsoft-access/ Access for developers: http://msdn.microsoft.com/access

Cross published from http://habaneroconsulting.com/Blog/Posts/Access_Services_in_SharePoint_2013.aspx













Comments

  1. Great summary of Access Services. I didn't realize that these wouldn't be searchable in SharePoint or that extra Azure storage was necessary on Office365.

    ReplyDelete
    Replies
    1. Thank you for your feedback. When you are using Access Services in O365 the Access Databases is automatically provisioned for you; thus no need to purchase storage/services from Azure. And, because the Access data is now stored in its own database the SharePoint search engine is not aware of the content.

      Delete
  2. Great article, thank you for the writeup. Access Services fails with an error of not being able to connect due to localDB or SQL Native Client issue when I try to do an Access 2013 web app and linked table to a Sharepoint list. Any thoughts??

    ReplyDelete

Post a Comment

Popular posts from this blog

How to enable Audit functionality in SharePoint 2010

“Microsoft SharePoint Server 2010 includes four information management policy features to help you manage your content: expiration, auditing, document labels, and document bar codes. - msdn.microsoft.c om” In this post I will focus on the audit capabilities in SharePoint 2010, more specifically how to enable the audit functionality. When the audit functionally is enabled it will automatically log events and activities that is done on your content. The content could be documents, records and other items, such as task list and calendars. Why Why would you enable auditing in SharePoint? There could be different reasons, and they often depend on which vertical (e.g. Public/Government) you are located in. Meet Regulatory and Legal requirements. Track how documents (and items) are used. Keep track of document history if documents are sent to the Content Organizer . When using the Content Organizer,  the document’s version history is deleted. The audit log will be your source to

How to add Content Types to a Document Set

After enabling use of Document Sets to your site and document library you will see that within the Document Set, only the default Document content type is available.