Access Services in SharePoint 2013…More ‘No Code’ Solutions

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.

For reference, Composite Solutions in SharePoint have traditionally been business solutions in SharePoint that leverage InfoPath Forms, SharePoint Workflows, SharePoint Designer, Business Connectivity Services and Reporting without the need for custom code development.

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.

Let’s take a quick look at how it works…

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.

Add an App

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.

Add an App screen

Once my new Access App is created we need to start adding tables to the using Access 2013 Desktop

Open in Access 2013

I can add tables manually or from existing sources, in this case I’m going to use a template for Asset Tracking.

Add Tables in Access

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.

Access Form Cretion

Here’s the same form in our SharePoint Online site.

Access Form in Browser

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.

PowerPivot with Access 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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *