Redmond Review

SharePoint Is the New Access

• By Andrew J. Brust
• 07/01/2010

Microsoft offers no shortage of options for developing so-called "forms over data" applications. Whether using data-binding technologies in the Microsoft .NET Framework, the Data Sources Window and its drag-and-drop capabilities in Windows Forms, Windows Presentation Foundation (WPF) and Silverlight, or ASP.NET Dynamic Data, Microsoft has got you covered. Really, the scenario of building data-focused screens and applications has been one that Microsoft has assisted us with since the dawn of Access version 1.0.

For a while now, though, there's been another option: SharePoint. Since Microsoft Office SharePoint Server 2007, it's been a solid forms-over-data contender. And with the recent arrival of SharePoint 2010 and its Business Connectivity Services (BCS) feature, SharePoint is an even stronger platform competitor for simple database applications. For SharePoint pros, this is great news.

While things have changed a lot over the almost-18-year time span since the initial release of Access, the core requirements of small data applications have remained remarkably similar. But the old Access world seems largely cut off from the new SharePoint one. Or is it?

Gaining Access to SharePoint Data
The reality is that Access and SharePoint are kindred spirits, and their feature sets bear that out. To begin with, the 2007 version of Access added significant SharePoint import/export capabilities, turning Access into one of the strongest rich-client apps available for maintenance of SharePoint lists. It gets even more interesting with Access 2010.

The new version of Access, rather than offering simple connectivity to SharePoint lists, provides a full development environment for SharePoint applications. The new "Web database" facility in Access 2010 allows Access developers to map nearly every facet of their skill set and technology base onto a corresponding facility in SharePoint 2010. The Access application becomes a SharePoint site; forms become HTML- and ASP.NET-based SharePoint pages in that site; new UI macros get converted to JavaScript code in those pages; data macros become SharePoint workflows; and Access Reports get converted to SQL Server Reporting Services (SSRS) reports against the generated SharePoint lists.

This is not a one-way conversion. After the Web database is published to SharePoint, it can be opened in Access again and further modified. Everything round-trips nicely, including client assets such as VBA code, which SharePoint simply ignores.

I think the Access team should be commended for creating the Web database facility. They've stayed faithful to the Access legacy of allowing easy creation of database applications with little or no code, and they've applied it to the modern Microsoft software stack.

In the process, they've addressed a number of the historical shortcomings of Access. The Access database format and Jet database engine are replaced by SharePoint lists, stored in SQL Server. SSRS is used, too. The Web replaces the practice of putting native Access databases on a file share and trying to scale them. VBA code is gone, and creating macros becomes an authoring process for JavaScript and Windows Workflow Foundation.

The Access team has shown us that technologies change but many requirements stay the same, and they offer their application as a bridge between the old and the new. I have to say, I'm impressed.

Outside Perspective
I'm also perplexed. How come something like Access Web databases doesn't exist in Visual Studio and .NET? Why is there nothing from the Developer Division that so elegantly stitches together so many pieces of its own technology stack, and makes them accessible in a way that C# and VB.NET do not? Why is all this so complex that it takes a product team in the Office group to integrate it and simplify it?

I think it's because .NET itself has become too complex, and the people building .NET perpetuate that complexity and sometimes even take pride in it. The team behind Access can afford to take a fresh look at what the Microsoft Server and Tools Division built, pick some of the best parts and break down the barriers in front of and between them. Their risk is lower, and their approach can be more pragmatic.

I get it, but I'm not satisfied. Innovative thinking shouldn't have to come from outsiders. The Developer Division should be transcending risk and doing more such simplification work itself. I commend the SQL and SharePoint teams for working with the Access guys; it shows sensitivity to the complexity issue and a commitment to solving it. I just hope that sensitivity signals a new beginning and not a mere anomaly.