Many of us in the Microsoft community started our career with Access.  I started my career building Access applications in Access 2.0 and have used various versions since then to deliver small business applications.  IT departments generally LOATHE access databases because they are not centralized, hard to incrementally deploy and have less than adequate security.  Yet from a business productivity perspective, there are hundreds of usage scenarios where Access provides a great solution.

One of the key challenges with SharePoint historically has been a bad choice between SharePoint lists (simple, quick, easy to change but not a relationship database) and custom developed SQL applications (expensive, difficult to deploy, improved performance and security, a real relational model).  SharePoint lists have also had challenges with large numbers of entries (e.g. display more than 5,000 entries).

Could Access 2013 help bridge the gap and yet work in a secure, managed environment like SharePoint?  Let’s try it out.

Creating an Access Database

When creating a database in Access, there are two types: a “desktop database” (e.g. like old versions of Access) and the new “web app”.  Desktop databases are saved to your local machine, web apps are saved to the cloud either to your sky drive or to a SharePoint 2013 site.  There are also lots of pre-fab templates that you can use to create databases:

Integrating SharePoint and Desktop Databases

If you create a desktop database, you can link to an existing SharePoint list.

When you integrate with this table, you can update the list directly from Access 2013.  Here is a basic task list that I was able to link to in SharePoint 2013:

I can also add fields to my table in Access 2013 and these new fields are added automatically to my list:

If I go back to SharePoint, you can see that my Project ID field has been added:

I can now also create relationships between the task table and a project table I have created locally in Access.  However, there is no referential integrity support between the two tables:

Given this is one of the key weaknesses of SharePoint lists and one of the key reasons to have a database, this is a pretty big disappointment!

Using Access to create a Web Application

The other option is to create a Access Web Application.  When you create this type of database, the tables are created on your SharePoint server and are accessible via the SharePoint interface.

When you launch your application, you get a web based data entry experience using a forms model that is completely web based and integrated into SharePoint:

These forms are editable in Access with an easy deployment model – click the launch button and any changes are deployed.

Again, you can integrate an existing SharePoint list, but the data is Read Only.  As well, you have no referential integrity between tables in Access and SharePoint lists.  In fact, in your web application, there doesn’t appear to be any referential integrity between tables at all, even if they were all created in Access.  Again, a pretty big disappointment for people wanting to create a real database and not just a basic list (which you can do just using SharePoint anyways).

This post was written by Chris Woodill
Christopher Woodill is the Vice President, Public Sector for Navantis. With over 15 years of experience planning, managing and implementing technology strategies in both the public and private sector, Christopher is responsible for public sector sales, strategy and delivery of solutions for our clients in healthcare, government, municipalities and education.