The WorkerThread Blog

we know stuff so you don't have to

Archive for January, 2010

Thoughts on Access Services in SharePoint 2010

Posted by workerthread on January 16, 2010

Back in November last year I gave a presentation to the UK Access User Group  on Access 2010 and in particular how it works with Access Services in SharePoint 2010.  I’ve been meaning to put something together on my thoughts on Access Services, both good bits and not so good.  Last week Robert Bogue wrote an interesting piece about Access Services and how he sees key usage areas.  Robert sees one main area of interest being as a reporting tool for SharePoint content, even content not originally created in Access.  I’m not sure I agree with him here, for reasons I’ll explain below.

First let me say that the Access team have put some clever features in Access 2010.  As with Access 2007, the emphasis is on a well-behaved Office application where customisation and development is ideally done without coding in VBA.  To this end, the Access macro editor has been completely re-designed, and now includes data macros which allow you to create events at the table level (similar to triggers in SQL Server).  There are also some interesting new navigation features which make it easy to create a modern looking application for users to find their way around.  Same with forms and report design – each of these has a “layout” design mode which is mandatory if you want to design for Access Services.  Using layout mode means designing forms and reports in pretty much the same way as you would an HTML table, or an InfoPath form.  There is a new option to create a “web database” which disables any Access feature which won’t be compatible with Access Services.  There’s also a compatibility checker which reviews all of the objects in your database and highlights anything which may cause problems when publishing to Access Services in SharePoint 2010.  At the bottom of this post I’ll include some links to videos and getting started tutorials which you might want to try out yourself.

Robert mentions the fact that Access 2010 web database reports are published up to Access Services using RDL (report definition language).  And in fact you don’t need to install full-blown SQL Server Reporting Services to get this to work – there is a reporting services add-in for your SharePoint 2010 server which you can install from a download here.  This can be run in local mode when using Access Services as explained here.  Once you have published your Access app, reports work in pretty much the way they would in the full version of Reporting Services, including pagination and report export options for PDF, Excel etc as you can see here:


Web database applications are also available as SharePoint site templates.  If you have installed the beta of SharePoint 2010 you will see several of these in the available templates list, here’s an example:


When you provision your new site it will look very much like it would if you had published it from Access – and you can also further customise the site in Access 2010.  There are similar templates provided in the current Access 2010 beta, so you can also try it out by using one of the Access templates to create your web database and taking a look under the covers at some of the design and macro coding techniques used (no VBA of course!).

And yet, and yet….

There are still several reasons why Access services might not be that attractive to a lot of SharePoint users.  First and foremost, as Robert says, Access Services is a SharePoint 2010 Enterprise feature, which will bump up your licence costs considerably.  And the appeal of Access Services as a SharePoint reporting tool may be diminished by the fact that SQL Server 2008 R2 includes SharePoint lists as a data source out of the box.  This means that you could install SQL Server 2008 R2 and integrate with any version of SharePoint including SharePoint Foundation 2010 (Windows SharePoint Services 4.0).  From what I have seen so far SQL Server 2008 R2 will also happily connect to SharePoint Server 2007 lists and will allow you to deploy reports to multiple SharePoint zones, so even if you aren’t considering a SharePoint 2010 upgrade you may want to consider the R2 release of SQL Server 2008 for reporting.

Then there’s the degree to which a published Access web database is locked down.  Most of the standard SharePoint 2010 site settings options are missing in the published site – if you want to continue customisation you need to do this in Access and re-sync changes.  Here is the standard menu of options:


Also, the look and feel of the Access site will be inherited from the Access database, not from your (possibly) extensively re-branded master page.  When I did the user group presentation, this was something that several attendees who also had SharePoint involvement were concerned about.  I DID notice that it the _layouts/settings.aspx and _layouts/viewlsts.aspx pages were still accessible though, so it was still possible to see all of the site settings options that had been hidden – not sure you’re meant to do this though as it could make further Access customisations difficult and it may be that this is locked down further in the final release.

As someone who has been doing a lot of work with SharePoint over the past three years, I would also have liked to see some tighter integration with standard SharePoint stuff.  For example, if you have a web database which assigns items to users (extremely common in SharePoint) I would want to use alerts, the out of the box personalisation features that we take for granted (custom views that use a [me] filter for example).  I couldn’t find any easy way to do this with an Access Service database.  Equally important for anyone thinking of trying this out, a published Access web database can’t be opened in SharePoint Designer 2010.  So if you want to use any of these features and you are moving data from Access your best bet could be to migrate the Access tables to SharePoint lists in a normal SharePoint site, then do your additional customisations rather than using an Access Services web database.  I can understand the reasons why Access Services doesn’t allow this (it needs to retain Access client fidelity) but it kind of eliminates a lot of the reasons why you want to use the SharePoint platform in the first place.  I suppose what I’m really looking for is a way of publishing the web database to SharePoint then disconnecting from the original Access app so I can continue my customisations on the SharePoint side.

Personally I think it’s unlikely that many existing, complex Access apps will be migrated to Access Services as there will be too many incompatible features.  It’s possible that some smaller departmental applications might get migrated, but this still require support from the IT Department which I’m not sure exists in lots of places.  And if I was designing a new application, first I would look hard at the new code-free development options in 2010.  We have what looks like a better version of SharePoint Designer, SPD workflows which can be re-used in multiple lists, list forms that can be customised in InfoPath, even (finally) options to enforce relationship behaviour in lists.  And many of these features will be available across all versions, so you won’t necessarily need SharePoint 2010 Enterprise.

Whereas Robert thinks SharePoint reporting is one of the key areas where where Access Services fits in, for me, I think the real opportunity is in hosted SharePoint 2010 solutions.  With Access 2010 you could develop an in-house application, or possibly a commercial one and make this available as both a rich client or easily extend reach using a hosted version of SharePoint 2010 enterprise.  This could have strong appeal to the many developers who have vertical market Access applications out there already and means they could offer a hosted browser based version of an application, even to small, distributed groups of users who can’t afford or don’t want their own in-house server. 

So that’s my take on Access Services.  I’m interested to hear what you think, particularly if you get to try it out yourself so as usual comments are welcome.  If you would like to try out the hosting option for yourself, you can get a free trial right now from Access Hosting.  Then all you need to get started is a download of Office 2010 beta.

Here are some links you might find useful of you plan to try out Access 2010 and Access Services:

Access Product Team Blog: Access Services Overview and Install Information

Access Product Team Blog: Publish to SharePoint (part 1)

The Access Show: Access 2010 Demo of Access Services and Web Databases

The Access Show: Publish an Access 2007 Database to SharePoint with Access 2010

Albert Kallal’s Access 2010 web database demo video

Posted in development, Microsoft SharePoint, Office, SQL Server, web development | 26 Comments »