So far in our SharePoint Forms blog series, we’ve primarily covered tools that operate with SharePoint Lists when creating forms. But for cases where data is highly relational, Lists are perhaps not the technology to use. Enter Access Web Apps, Microsoft’s answer to providing relational database capabilities inside of SharePoint 2013, complete with a SharePoint form for each data table. In this walk through, we take a look at how they can be used in a SharePoint 2013 Office 365 Site.
First off though, some pros and cons about the Access 2013 Web Apps
Pros of Access 2013 Web Apps
- You can build them directly in Access 2013
- They support relational data on multiple levels
- No coding is required
- The backend for them is SQL Server 2012, allowing for scalable data storage and access
- Security is based on SharePoint groups; you can determine who has edit vs readonly access to the Access Web App through the standard SharePoint Visitors and Members groups
- Microsoft offers data table templates, speeding the creation of Access Web Apps
- The form designer tools have Microsoft Access’s trademark ease of use
- You can package your app to sell on the Microsoft App Marketplace ($$$)
Cons of Access 2013 Web Apps
- Records stored in Access Web Apps cannot trigger or be part of SharePoint 2013 workflows. If you want to build a SharePoint form that involves significant business processes, Access Web Apps might not be for you.
- Security setup is basic and somewhat limited. First, it isn’t possible for users to own records. Second, there isn’t a way to separate who can add a record vs. who can edit a record. Third, there is no way to apply edit vs readonly permissions to specific tables in an app. If you can edit records in one table, you can edit records in all the tables within the app.
- The app owns the database. Don’t expect Access Web Apps to provide a front-end to a centralized database that already exists.
- For now, creating Access Web apps is a little buggy. When creating a very simple app, Access 2013 crashed on me twice, and I lost some work. Hopefully Microsoft will iron out the bugs in the coming Office 2013 service packs.
In short, don’t expect Access Web Apps to power any application that requires complex business processes and/or granular security. But if you simply need a convenient way of sharing relational data with your SharePoint user base, Access Web Apps might do the trick!
Creating Your Web App
That said, let us walk through creating an app. When you open Access 2013, you’re greeted with the ability to create a custom web app or create a new web app based on four displayed templates. You can also use the search box to search for app templates online.
This in my estimation is brilliant. Users of Microsoft Access might not always grasp how to design a relational database table structure, but they can certainly take one that already exists and add fields to tables where need be. That said, I found the number of templates available for Access Web Apps to be a bit lacking; for now, it looks like more templates exist for their desktop counterparts.
Let’s say we click custom web app. You will get a prompt asking you for the SharePoint site the app will be deployed to. If you are creating an app for an Office 365 SharePoint site, you must be logged into Access 2013 with your Office 365 user account.
Clicking Create will setup a SharePoint subsite for your app, and at that point you’re ready to begin building.
* Note that it took a day or so for my SharePoint Office 365 site to be ready to host Access Web Apps. If you’ve just created your SharePoint Office 365 site, you might want to wait a day before creating Access Web Apps on it.
Creating an Access Web App is very similar to creating a desktop app. You create a table first, and Access 2013 will automatically create a Datasheet and List view for your new table. As you can see below, I created a basic table for storing Vehicle information, and access created the initial views for me.
After creating a table (which as noted above creates your default views), clicking Launch App launches the web browser, and behold, in SharePoint 2013, you have a working database application!
Adding subtables and their corresponding interfaces is easy. Just add a table that references your first table as a Lookup and Access 2013 largely handles the rest, automatically adding subrecord navigation to any view that contains the referenced table, and creating default List and Datasheet views for the new table. Of course, you can tweak the layout as you see fit, and this is a good thing, as Access 2013 can crash on you occasionally when it is trying to accomplish design tasks automatically.
Some Business Logic is Available
Access Web Apps can also do more than provide simple relational CRUD access. Actions can be triggered after field updates (among other things). Actions that can occur include:
- Triggering message boxes
- Triggering record saves
- Changing the currently displayed view
- Triggering a re-query of the displayed data
Forget about writing custom VBA or complex macro code though. Instead, Microsoft gives you a condition / action framework for configuring business logic, somewhat similar to how you configure logic in SharePoint workflows.
Access Web Apps provide a quick way to create relational databases on SharePoint 2013 within an afternoon, particularly if your SharePoint site runs on Office 365 (there’s a lot of back-end IT work that goes into hosting apps locally). I’d like to see Microsoft publish a lot more templates for the various types of apps that can be created, but then again, lack of templates mean an opportunity for Microsoft partners to step up! That issue aside though, the shear speed you can get a rich looking app deployed is pretty amazing.
On the other hand, the fact that Access 2013 crashes during the app creation process is a bit of a hinderance. And there is definitely a flight-ceiling for what you can accomplish with Access Web Apps. If you want your app to send an e-mail (a pretty basic piece of business logic) after a record is added on one of its SharePoint forms, you need to use a third-party solution. And granular security just isn’t there. You might want a user to be able to edit records in a subtable, but not the main table. Forget about it, not an option.
But then again, Access Web Apps are an evolution of desktop Access databases, which share similar limitations. If you can plan around them, you can deploy relational databases on SharePoint very quickly, complete with very attractive SharePoint forms! At the very least, Access Web Apps can be used to prove a concept before serious dollars are spent on an app idea or a custom SharePoint forms application.