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, 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).
"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.
It's easy to create an Access App
An Access App can be added to your site in three steps;
- Design (database schema and logic)
To add an Access App, go to Site Action and click "Add an App"
Find and select Access App:
Give it a name and click "Create".
The next step is to create and design your database schema:
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:
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 Lists 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:
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.
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.
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 — e.g., Crystal Reports, Excel, and Power View.
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.
White paper – Office 2013 – Access Services Setup for an On-Premises installation:
Microsoft Access blog:
Access for developers: