If you are like me, the thought of using Microsoft Access for ‘No Code Solutions’ in SharePoint 2013 probably elicited an eye-roll of sorts. We are talking about Access, the same Office application that created the dreaded .MDB files that have cluttered hard drives for years and caused many IT headaches. However, I thought I would give it a closer look after seeing some pretty impressive presentations of its use at this year’s SharePoint Conference and because I’m looking for what has become of ‘No Code’ or Composite solutions in SharePoint 2013.
Like Excel Services, Visio Services and Word Services in SharePoint Access Services allows you to deploy Access Databases to SharePoint and allow users to access and use them without the need for the Desktop Client. The fact that you can host and use access applications on SharePoint as opposed to having an MDB on your hard drive or shared drive reason enough to give Access another look. Oh by the way, the Access Services App in SharePoint 2013 actually creates a new SQL database for each Access Services App that is added which provides much better scalability. Still not convinced? That’s OK neither was I but when you add in the new form designer, ability to run macros and the ability to use reporting tools like Power BI this becomes a very interesting solution for no-code solutions in SharePoint.
To create an Access Services (assuming your SharePoint Admin has configured Access Services) simply ‘Add an App’ in the site where you want to host the App. Note: All Access Services Apps will appear in the Access Maroon color.
In this example, I’m going to create a new App from scratch and use a predefined template once I have open the new App in the Access Desktop, but you can also upload an App package as the basis for your new App. App Packages are a Save Option in the Access 2013 Desktop.
Once my new Access App is created we need to start adding tables to the using Access 2013 Desktop
I can add tables manually or from existing sources, in this case I’m going to use a template for Asset Tracking.
In the Asset are 3 tables (Assets, Categories and Employees) and a number of different forms. We can easily modify the forms to add additional fields, new buttons and new views all within the interface and they will render as a webpage when we access it in SharePoint.
Here’s the same form in our SharePoint Online site.
The bonus here is that we can now connect to Access Databases as a data source in PowerPivot and create PowerView reports. You will need to enable the Read-Only Connection in the Info backstage with Access 2013 in order to connect Excel to the data.
As you can see, there are a number of compelling features in Access 2013 and Access Services in SharePoint 2013 that appeal to the ‘no-code’ solution crowd; in particular, those that have a lot of experience creating business solutions in Access. The fact that it creates and stores the data in SQL, provides web access to forms, an easy to use form design interface and the ability to connect to the BI tools within Excel and SharePoint are major improvements. The most notable downside here is it creates a fairly ‘silo-ed’ solution that doesn’t allow for easy interaction with SharePoint lists, libraries and workflows which makes it’s overall usefulness a bit limited.