Access Web Apps Review

*****UPDATE 28th March 2017 - Microsoft have announced the retirement of Access Web Apps and therefore we cannot recommend their use *****


Access Web Apps (AWAs) were first released with Office 2013 and were a replacement for earlier attempts to make a Web version of the world's most popular database. However, rather than storing data locally and having a Windows front end AWAs are hosted in Sharepoint / Office 365 and data is held in SQL or SQL Azure. I have personally used AWA to build a set of Apps for Sharepoint which are sold on the Sharepoint App Store - and in this article I aim to compare AWA to other offerings already reviewed here.

I have said that I have been using AWA for a few years now to build solutions for sale. I thought it would be worth just mentioning the main reasons that I chose this technology at the time:

• Very fast No Code 'development'
• Robust deployment and security with Sharepoint Online and SQL Azure
• Opportunity to distribute and sell through the Sharepoint App Store
• Simple management of updates and fixes

Let's come back to this at the end of the review to see if these considerations are still valid.


Building your App

Unlike other platforms I have reviewed so far, with AWA you use a Windows application to build and deploy your solutions - Microsoft Access. When you select New from the menu in Access, you have the option to create traditional desktop databases or the newer Web Apps - from either some supplied templates or starting from a blank canvas.


Pasted Graphic 10


If you choose to create a new app from scratch, then the first thing that you need to do (after naming your app) is to create some tables:



Pasted Graphic 1


Here is an example of a table created for a Contact Management app:


Pasted Graphic 2


Once you have created your table, some magic happens - in that Access creates two Views (a data sheet and a form) and adds them to your app's menu automatically:


Pasted Graphic 3

This makes getting started with your new app very simple and quick - but at any time you are able to edit these using the form designer. Another clever feature is that modifying your table will also update the views Access created (unless you edit them yourself in which case you have to add new fields yourself). This gets even more sophisticated if you add a new table linked to existing ones - for example you may want to add an Activities table so that you can record conversations and other interactions with you clients. If this table has a link to Contacts then Access will automatically add a Related Items Control to your Contacts view from where you can view, add and edit Activity records:


Pasted Graphic 4


Customising your Solution

Having created a basic app which is now available to anyone who has access to the Sharepoint Site it was created in, you will probably want to customise it. This is where Access Web Apps excels when compared to most of its Online competition. Here's an example of a Dashboard screen from one of the applications I have created:


Pasted Graphic 5


This screen has information drawn from several tables in the app along with some stats etc. This has all been achieved with help of the Query tools, User Interface Macros, Data Macros and Screen Designer provided in AWA. Here is a modified version of the Contacts view (shown above) in the screen designer:


Pasted Graphic 6


You can see that fields have been moved about and that you have a list of available fields on the right hand side from which you can add. In the designer you can add many different types of field, sub views and Related Items Controls - as well as buttons. Each of these 'controls' can have macro actions associated with them when certain events occur (for example after a field in updated, when a button is clicked or when the View itself is loaded). For example, if you wanted to colour the background of the section headers in the above view you might have an 'On Load' macro such as this:


Pasted Graphic 7


This is called a UI (User Interface) Macro - and these can be used for a wide range of things effecting the UI - showing and hiding optional fields, changing the text on a button - and importantly triggering the other type of Macro, a Data Macro.

Data Macros are at the heart of the real power of Access Web Apps and are used to manipulate data in the database in many ways including:

  • Validating data
  • Creating, deleting and updating records
  • Looking up information for use in other macros or data macros
  • Sending emails (Online only)
  • Performing calculations
  • etc

Here's an example of a Data Macro which sends an email based upon the Activity table described above:


Pasted Graphic 8


The next section of the Data Macro now looks up the email address to send the alert to and then sends the email:

Pasted Graphic 9


AWA also inherits most of the query capabilities from it's desktop cousin. This is wholely unlike the other platforms I have tested recently which are only able to use lookup / rollup type fields to gather summary data (or data from other joined records). Querying is a fundamental part of relational databases and in the case of AWA this is made quite approachable for relatively inexperienced users because of the query designer.


Application Management and Deployment

One of the key things missing from most web databases is the ability to manage development / test and deployment of your app. Where other platforms allow you to copy an app and try out changes on a copy, in the end, changes have to be made to your LIVE system. This is quite a dangerous process and so Microsoft have implemented a publishing facility into AWA which allows you to make changes to a development copy of your app and, when you're happy, export a new copy of your app and automatically apply updates to your live application. This is a huge advantage for all but the smallest solutions.


Reporting and BI

This is one area where AWA is not that strong - however, as the data is stored in SQL Azure you can connect to the data with tools like the excellent Power BI - or pretty much any other reporting or BI tool of your choice.

Having said that, there are View types within AWA with which you can show users summaries and lists of their data within the app. Datasheet views also have the ability to download data directly into Excel.

Finally, you can also connect Access Desktop to your app so that you can use the reporting tools it provides.


Integration with Other Services

AWA doesn't have much in the way of Native integration with other datasources or service with the exception of being able to connect (read only) to Sharepoint Lists. However, using Microsoft Flow or Zapier it is possible to connect to a huge range of online services from Facebook and Twitter to other databases and accounting systems.


Mobile

One area in which AWA is weak is support for mobile platforms - particularly phones. Apps are relatively useable on tablet devices but not really on a phone. However, Microsoft has just released Power Apps which seems a very usable tool for producing mobile add ons to business solutions (although as of the time of writing I wouldn't want to try and build an entire solution with it).


Summary

The difference between Access Web Apps and most other platforms I've looked at is the degree to which a solution can be customised and automated without having to resort to any code. Data Macros, in particular, are very powerful. You really can build fully featured applications with AWA which are robust, secure and, most importantly, users enjoy using.

For those new to databases, the learning curve is relatively gentle and continuos - by which I mean there is no point where you suddenly have to learn massive new skills before you can move on.

Once your app becomes a little more complex the deployment options really come into their own making sure that you don't have to make modifications to live solutions.

So from a technical / product perspective I have no trouble in recommending Access Web Apps - especially if you have an Office 365 plan which includes Access Services already and you can therefore run AWA apps at no additional cost. My concern is that there have been no real developments to the platform (despite it being relatively new) for about 2 years now (there were no real changes in Office 2016).

Having said that, for my own use AWA is still the only platform I have found which meets the requirement I laid out at the start of this article - although I appreciate these are not considerations that would be relevant to all users where an alternative approach such as Podio, Airtable or Knack may be a better fit. I will be comparing the products I have reviewed so far in an upcoming post - and try to identify for readers which would be the most appropriate tools in different circmstances.



blog comments powered by Disqus