Access Services in SharePoint 2010 or: How I Learned to Stop Worrying and Love Access 2010

author Richard Fennell calendar Oct 19, 2009 · 6 min read · Uncategorised  ·
Share on: linkedin copy

So what I have I been doing of late? The blog has been a bit quiet. Well I have been having a good look at Access Services in SharePoint 2010. This has been an interesting experience, as I am not historically what you might call an avid Access developer.

Like most .NET developers I had looked as Access as more of a file format than an application, something from the past. Something that I might use for a small data store, maybe in a web application hosted on a cheaper ISP that does not provide ‘a real’ SQL DB, or where an XML data files don’t seem right, often because I just can’t be bothered to work out the XPATH. When using Access as a data format it seems easier to get at my data using basic hand crafted SQL commands or maybe at most via a OLEDB Data Adaptor/DataSet. All very old old school. Thinking about Access in this way just seems an easy way out, playing it safe with the knowledge I have. I don’t for a second propose that this a good idea, you should not be looking at using any technology just because it is there and you already know it. There are obvious downsides, using Access in this manner meant that from the ADO.NET developer side I could not:

But equally, by treating Access as just a data format I was not able to make use of it as the Rapid Development tool it is. I was too hung up in the unpleasant idea of an MDB sitting of a server being poor at locking and saturating the network with unwanted traffic. I was not even considering Access as a front end to a MS-SQL solution, and it is not as if that is new technology, it has been around for ages. I was just sitting happily with my prejudices.

I don’t think this position is that rare for .NET developers these days. Access seems just looked down upon as something old in the Office pack that is best ignored, no good would come of using it in a business environment.

So enters Office 2010 and SharePoint 2010 Access Services, for me this changes the game. For those who don’t know this technology, you can create an Access database locally on your PC then publish it to SharePoint. Tables become SharePoint lists, macros become workflows and forms well become forms. Access becomes a RAD tool to create data driven SharePoint sites.

So how has this new technology been working for me? Well I can’t say I have grown to love the Access client, but I think that is mostly down to that fact that I am still not thinking right for it. Access is all about data binding, you don’t have to think about what form fields need to be copied to which DB columns, the wizards make a really good attempt to design forms for you based on the relationship of the tables in your DB and this just all seem unnatural to me. I think this is because I am usually working with design patterns to reduces the linkage between forms and data to a minimum e.g. the MVC pattern, and so consider this good practice; automated data binding seems seems wrong. So in Access I keep wanting to build things from first principles, but this is just not sensible. Better to let the tool get you close and then you add the polish, put away any thoughts of implementing design patterns as you would in a language such as C# or VB.NET.

I think this is the key to the degree of irritation I feel with the product, if you have got used to architecting from the ground up, especially in a Test Driven Development style, you have to turn everything on it head. It feels like you are cheating, not doing the job properly.

But wait! look at the benefits. A while ago I was involved in a project to provide a resource management data driven web site that was hosted within SharePoint. It contained the usual things, data entry forms, links to SQL and reports. It took a couple of weeks to build. I think I could write the same system in Access with SharePoint 2010 in an afternoon, and would be happy to have a client’s business analyst sit next to me while I did it, in a pair programming style, to design the forms, report layouts and columns as I went along. For the smaller scale data driven site Access Services is a great tool, but obviously it is not perfect. I do keep hitting points where I think ‘if I were in C# I could just do that’ but then I remember ‘yes but it would take would have taken me three days to get here not an hour’. Most project don’t need that last 10-20% you can only reach on .NET custom code, the client with be far happier with 80% done quickly and flexibly rather than 95% done a lot later. Also we have to factor in my relative lack of experience with Access as a RAD tool, reducing the productivity that could potentially be achieved by a more experienced Access developer.

Actually the bulk of the time I have spent has been on looking at how you can extend Access Service to reach that last 20% of functionality, and it not that hard. The key to remember is that the Access Services are just built on standard SharePoint objects. Ok there is a new service running to render the pages, but underneath there are just SharePoint lists and workflow, and where these exist there are events that you can programmatically handle. I have found that by trapping events such as ItemAdd() for the Access created SharePoint lists there is no real limit to what you can achieve via the SharePoint Object Model. And this development process is made even easier by the new Visual Studio 2010 templates for SharePoint features. If nothing else the fact that all the templates create a WSP for deployment as standard makes for far more robust feature development.

There is one major difference between a standard SharePoint site and one created by Access, and it is that SharePoint Designer cannot open the Access site. I thought this would be an issue when I first heard about the limitation, but it turn out not to be. Anything you might have wanted to do in SharePoint Designer you can do quicker and easier in Access for this type of data driven site. Ok the range of things you can do is more limited, but again you get that 80% you need with much less pain.

So how has my experience with Access 2010 been? Exasperating, frustrating but undeniably productive. I am not sure it is the right product for an ISV style company who want to roll out single solution to many client sites (but it could be used for this if needed via the SharePoint site template gallery); but for a smaller data driven site (with or without custom extensions) written within an IT department it is a very strong contender. Taking Access in many ways back to it roots.

So if you need small data driven sites I would suggest you put aside your prejudices and have a look at the beta program for Office/SharePoint 2010, I think you will be surprised.