Access 2010 And SharePoint – Welcome To The Hybrid Access Application

Now that we can talk about all the features in Access 2010 I think I have to place my marker immediately on what I believe is the BIG story in this release.

For the past 3 or so years I have kept hearing internally at MS about the Web story in what became Access 2010.  The idea seemed to be that in order to sustain a future for Access it was necessary to “tie its can” to SharePoint’s bumper and offer itself as a somewhat functional database tool for lists and relatively small data requirements inside SharePoint.  I always had trouble with that idea because I was concerned that by doing so they would marginalize the very community that Access needs to survive, namely the worldwide non-network of people using client-side Access (often secretly) to produce Departmental Solutions.

But nonetheless I jumped on the bandwagon a couple of years ago.  Reading the writing on the wall I stuck my neck out and developed a serious application using Client-Side Access 2007 with SharePoint 2007 Lists as the backend.  And it worked !!!  In fact that application has been in production for over a year, and with a little help from Terminal Services for those users spread around the world (literally), this application has worked and worked well (despite the kludges I had to employ to get everything to work well).

But in the background of all of this was with the expectation that Access 2010 would provide a far better story.  Well it’s true !!  But not in the way many expected.

Welcome To The Hybrid Access Application

While a lot of the talk will revolve around the pure Web-story in Access 2010 I believe that is not the REAL story – and any effort to make that the real story would be misguided.

The conception of the Access 2010 Client – SharePoint 2010 Server integration is not only brilliant it was also very, very ballsy.  I cannot imagine a bigger leap of faith than what they did.  That is the REAL story of Access 2010 and Access Services.

In the end Access 2010 allows the traditional client-side Access developer to stage their entire application inside SharePoint on a Subsite created by Access Services.  When an Access application is “Published” to SharePoint all objects (tables, queries, forms, reports, etc.) get stored inside the SharePoint subsite. 

The user needs only a small (200+ bytes) shortcut to subsequently launch this application from SP.  But wait – the first time a user launches the application, Access Services reconstitutes (they use the cute word “Re-Hydate” at MS) in a folder on their client machine C: drive in binary format (not XML).  Then Access launches THAT version locally.  But while the data is cached on the local machine (again in binary format) this data is directly synched to the actual tables INSIDE SHAREPOINT. 

Yes – the data, although cached locally, is synched in real time – BOUND – to the actual live tables inside the SharePoint site.  Any changes to data is instantly synched with the Server version.  So the user gets the benefit of using a cached client-side version of the full-featured client Access application but the data is natively staored on a SharePoint server.

Next time the user launches the application, Access Services synchronizes the local cached version with the SharePoint site and only takes action on objects and data that might have been changed since the last visit to the site.  Therefore in most cases subsequent launches will be close to as fast as when using a local version of the data.

At the same time the developer (or developers by the way) can make changes to the application both front and back and then Synch their changes to the Server.  Subsequent visits to the application by users will then automatically drive those changes to their local cached version to keep them in synch as well.  No more re-deployment hassle !!

In order to even more enhance the performance of the client app, Access Services has its own special caching mechanism ON THE SERVER that works in conjunction with the local cache while the user is in the application.  It is my hope that this will allow client-side Access applications to operate across a worldwide Intranet EVEN using only a VPN connection.  If that is the case (and it is something I have not been able to confirm yet), then suddenly we have the ability to deliver Access applications to anywhere across a corporate Intranet with total real-time concurrency and performance that will be more than acceptable (especially when one considers the distances involved).  Getting access to Terminal Services or Citrix is a pretty easy process in most Corporations anyway if necessary.

Certainly an existing Access application cannot be Published to SharePoint Server without a few changes.  Indexes, relationships, lookups, primary keys will all have to be changed but these requirements will be easily defined and the Compatability Wizard in Access is a BIG help for that.  The pain is minimal and is all manageable (nothing is ever totally FREE in life anyway ). 

One important point is that IF the application is actually backended on SQL Server, those links can be preserved and the application can live on SharePoint without even using Access Services tables on the Subsite. But having the application live and be deployed through SharePoint using Access Services will still be the way to go.  In fact applications that started as Access MDB or ACCDB backends can migrate to SharePoint Tables and then up to SQL Server tables as demand requires …. all that with virtually no changes to the Front-End objects of the database.  Think about that one……

In the end Access 2010 provides the existing Access developer with a path upwards to the wonderful Browser-based world without giving up any of the capabilities of the Client version of Access.  In effect what we are looking at here is what I would have to call a true “Client-Server” scenario – one that truly puts the processing and the objects always in their proper place to produce the best results – how often have we really seen that? 

This is a big deal (IMHO).

Dick