SharePoint 2013 Snippets: Access Services


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 (5)

  1. paul says:

    Jessica – You say you can access the SQL server database used in the Access app with BCS.  How would you do this – I have tried trying to create both External Content Types and Data Sources using the connection and credentials listed for the Access database but it doesn't seem to allow these.

    Can you help, as this would be a very neat feature?

    Thanks Paul

  2. Andy Maggs says:

    Nice article, way better than the Technet overview you link to which amounts to 2 sentences in 1 paragraph!

  3. Vincent Perera says:

    Access 2013 sounds to be quite interesting. I have just signed up for a free SharePoint site with
    http://www.cloudappsportal.com and I can’t wait to use it. Thanks for this helpful article.

  4. Joe says:

    If I have Access 2010 client and I publish to Sharepoint 2013 server, would this apply? Or do I need to use Access 2013 client? Sorry if this is a stupid question.

  5. wewerer says:

    http://www.screencast.com/t/GFxDAe97m
    https://www.pinterest.com/pin/160511174196348200/
    https://www.facebook.com/1487873454868844
    http://www.screencast.com/t/b1xCZv9vL2
    http://www.screencast.com/t/Gyyao5QMX
    https://www.facebook.com/1483721111950488
    http://www.screencast.com/t/f1aFJrl7l6
    https://www.facebook.com/1485704191754503
    https://www.pinterest.com/pin/442830575838906399/
    http://www.screencast.com/t/wmzKTw4Vm4ws
    http://www.screencast.com/t/9i1I0V8J6ZyU
    https://www.facebook.com/1487847344871455
    http://www.screencast.com/t/eyJ4DyB2N9CL
    http://www.screencast.com/t/wX3FTjMrZ
    https://www.pinterest.com/pin/160511174196347934/
    https://www.facebook.com/1485234465134809
    https://www.pinterest.com/pin/160511174196348125/
    https://www.pinterest.com/pin/160511174196344265/
    http://www.screencast.com/t/FTz5V5pi
    https://www.pinterest.com/pin/19421842120367786/
    https://www.pinterest.com/pin/503840277041035380/
    http://www.screencast.com/t/uSRsyIpmv2E
    https://www.pinterest.com/pin/442830575838905645/
    http://www.screencast.com/t/2X6QAtsmSg
    http://www.screencast.com/t/OEYeHfKjk
    http://www.screencast.com/t/pW7niAi1R
    https://www.facebook.com/1496462384009051
    https://www.pinterest.com/pin/350647520966517922/
    https://www.facebook.com/1485763201748602
    http://www.screencast.com/t/Ek2Fg2dk8o3
    http://www.screencast.com/t/EKuKMXJcY0xa
    http://www.screencast.com/t/bjfCPnF1v
    https://www.pinterest.com/pin/160511174196348362/
    http://www.screencast.com/t/LNnvsyK5
    https://www.pinterest.com/pin/503840277041035889/
    https://www.pinterest.com/pin/350647520966530957/
    http://www.screencast.com/t/oWn51kl0N
    https://www.facebook.com/1483719111950688
    https://www.facebook.com/1493860104269136
    https://www.pinterest.com/pin/19421842120361760/
    https://www.facebook.com/1496453754009914
    https://www.facebook.com/1493780864277060
    http://www.screencast.com/t/0ksJbtouu
    https://www.pinterest.com/pin/148478118942136856/
    https://www.pinterest.com/pin/350647520966519649/
    http://www.screencast.com/t/5qmEdjQB
    https://www.facebook.com/1493783850943428
    https://www.pinterest.com/pin/148478118942136420/
    https://www.pinterest.com/pin/442830575838892881/
    https://www.facebook.com/1493855967602883