Share via

SharePoint 2013 Snippets: Access Services

  • Article

Access Services is an interesting topic in the 2013 context. When this was described to me, my first reaction was, “but we could do that in 2010.” Then you scratch the surface and discover that everything has changed under the hood.

The point of Access Services is to take an Access database and put it into SharePoint – giving you the ability to manage, secure and control databases that would otherwise have existed on someone’s hard drive away from the control of IT. To make changes to the database, for example to add new tables or change the columns of a table, you need to open the database in Access, but by putting the database on Access Services, other people can get access (no pun intended) to the data and update records within the database.

This probably sounds very familiar if you’ve done anything with Access Services in SharePoint Server 2010. The difference: in SharePoint Server 2013, an Access Services database is actually a SQL Server database. When you create an Access Services app, SharePoint talks to a SQL Server instance (it has to be SQL Server 2012 but doesn’t have to be the same SQL Server that the content databases are on) and creates tables within SQL Server for the tables in the Access database. What you end up with is a SQL Server database with a user-friendly front-end in SharePoint.

It’s really easy to create an Access Services database in Access – simply choose to create a web app from the start screen of Access. There are various templates for tables, forms, views etc. to get you started. For example, if you choose to add the Orders template, it will create several tables and views for you to track not only orders but the associated products and customers, building in all the necessary relationships. You can also add tables from existing sources – for example a SharePoint list. So you could create a link to a SharePoint document library and reference those documents from within your Access Services table.

The whole Access Services database gets packaged up as an app – the official terminology refers to them as apps rather than databases. This means it’s given its own site within your site collection. It also means you can package it up – so you can develop on one environment and redeploy to another. You can also design something in Access, package it up and put it on the app marketplace.

There is a slight annoyance to the fact that it’s an isolated app. It doesn’t seem to be a direct way to embed items from the Access Services app onto an ordinary SharePoint page. For example, if you want to display an Access form in a web part, there doesn’t seem to be a straight-forward way to do it directly in SharePoint. The answer seems to be to use BCS, which is fortunately easy because, as stated above, the data is in a SQL Server database. In fact, because it’s a SQL Server database, lots of other things (like reporting and AlwaysOn high-availability) which are native SQL Server functionality suddenly gets a lot easier.

If you want to know more about Access Services in SharePoint 2013, there's an overview on TechNet.

Comments