Part 1 – Introduction on a series of blogs about building database solutions for SharePoint

Who am I?

My Name is Julian Kirkness and I have been an Access user since version 1 (released in 1992) at which time I was looking for suitable technology to build an HR System for sale to clients. I finally released a product (and company) in 1996 – based on Access 95. The business grew over the years and I finally sold it in 2010.

Recently, I found myself in a similar situation – looking around for technology to build another product and business. Of course any new software product needs to be web based, as well as being easy to host in the cloud.

To my surprise I found myself considering Access 2013 and the newly introduced Web Apps and was the first person to publish Access Web App based solutions for sale in the Sharepoint App Store. I became an Access MVP in July 2014.

Access Web Apps

Access 2013 Web Apps…

I am not a Sharepoint expert by any means, but with the platform forming the foundation of many user’s IT strategy, a relatively straightforward means of producing database applications in that environment has long been overdue.

Access 2010 introduced rudimentary features for building web database applications but these were limited to connecting to data in Sharepoint Lists and provided limited functionality. However, with the 2013 release, Microsoft have provided the ability to build web databases connected to SQL Server / SQL Azure and host them in Sharepoint. This is huge because it allows for robust storage of data – and yet users need know nothing of the complexities of SQL Server in order to create applications!

So the main reasons I chose to use Access Web Apps to build my new apps (and business) can be summarised as follows:

• Very fast 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 via the app store

At the moment, I believe that Access Web App (AWA) tecnology is the only platform for building web applications which fulfils all these criteria and I am still delighted with the decision to use the technology.

What can you do as a beginner?

Building Solutions for SharePoint – Custom Web App

As you can see in the above screenshot, you can create databases either from scratch or you can use one of the templates shipped with the product – the latter is probably a good place to start – if nothing else to let you play with the tools and see some examples of what you can achieve. However, I’m going to walk you through the process of creating a simple app to record your contacts and their related activities (emails, calls etc). First click on Custom Web App in Access 2013 (you can also create a new app from within the Site Contents page in your Sharepoint site).

I am going to create an App called Contacts App for Blog – and you will eventually be presented with the Access designer in the Add Table option:

Building Solutions for SharePoint – Add tables

Tables can be created from a template or from scratch – here’s an example of a Contacts table I’ve created myself:

Building Solutions for SharePoint – Contacts table

Saving the table causes Access to automatically create a new form and menu option, as follows:

Building Solutions for SharePoint – Access Web App

In a few more minutes, I created another table (Activities) in which I record notes of conversations, emails etc. with my contacts – the key here is that when creating the Activities Table, we simply create a Lookup field to connect it to Contacts:

Building Solutions for SharePoint – Lookup field

Here I have shown the table design along with details of the Contact lookup field – notice that I’ve selected “Delete corresponding records…” – this sets up a Cascade Delete in the resulting SQL database so that Activities are deleted when their associated Contacts are deleted.

As soon as the new table is saved, Access will again create the new views and menu options – and because the tables are linked it has also created something called a related items control on the Contacts view:

Building Solutions for SharePoint – related items control

As you can see, we now have a list of activities relating to each contact and the ability to edit them (simply clicking on them) or creating new ones (Add Activities) – using a pop up screen as below:

Building Solutions for SharePoint – Add Activities

Everything we have done so far has been automatically set up for us by Access – but we still have a fully functioning App capable of recording dealings with our customers. We are going to leave it there for this first post – but if you want to follow along, why not create your own copy of this app for yourself.

In the next post, we will be looking at how to customise the screens that Access has provided for us and how to build some automation with macros. We will also look at how we can begin to build some information for managers with ‘Apps for Office’ graphs.

It is quite possible to build fully featured applications with AWAs and over the next few posts I hope to show you how. To give you an idea of what is possible though here is an example screen from our KasCur PRO CRM app:

Building Solutions for SharePoint – Dashbaord in Access

About the Author:

Julian Kirkness

Julian Kirkness has been an Access user since version 1 and has spent many years developing and marketing Human Resources and CRM applications built in Access and Access Web Apps.

His first business started in 1996 with an HR application built entirely in Access 95. This business grew considerably over the years as did the product – moving to a SQL Server back end and ultimately to a fully Web based solution. Selling the business in 2010, Julian had a period of inactivity (due to non-compete clauses) and about 3 years ago started looking for technology to build new applications and a new business.

He decided to use Access again – in the form of Access Web Apps (AWAs) this time – because of the uniquely rapid development process, combined with the robustness of SQL Azure and Sharepoint. The ability to market the apps through the Sharepoint App Store was also critical.

He quickly became the first person to publish apps built with the technology on the App Store and so far has 4 apps available. For further information see http://software.kirknessassociates.com.