When we talk about collaboration in the workspace the discourse tends to focus around SharePoint, Word and Excel, Lync and social components. What’s absent from that conversation is Microsoft Access. I’m not sure why this is. Access has been around for a long time and is still part of the Microsoft product suite. For some organizations, Access holds business critical data; for others, it’s an easy, tried and true way to capture, store, and report on data.
There’s many problems with how we treat Access currently. First, most Access applications reside on network drives. This thwarts collaboration in too many ways to count in this blog post. The main collaboration inhibitor is that the database is isolated from other resources - be it people or data. Second, Access databases tend to be built on average about a decade ago, ported over to a new release of Access and the knowledge and inner-workings of the application are held like state secrets by one person.
This leads to what I call Access fiefdoms. Organizations who utilize Access applications tend to be composed of several fiefdoms. I’m not talking about Game of Thrones power struggles here; instead organizations end up with a bunch of siloed solutions that ultimately will depend on a single resource to maintain them. Once that resource leaves the department or the company, the app tends to wilt and ultimately die.
SharePoint 2013 can curtail these Access fiefdoms via Access Services. At a high-level, Access Services is functionality provided by SharePoint, with a little help from SQL, that renders Access web applications on SharePoint. In 2013, Microsoft has changed the relationship on how Access and SharePoint integrate and I believe there’s something in Access Services for everyone. So without further ado, let’s break it down by role.
- Single Point of Collaboration
- If you’re already storing files in a team site and use Access, it coalesces elements of daily duties to a single spot.
- Don’t need to worry about file-level locks. Multiple users can be working simultaneously.
- Access Apps can pull in lots of data sources
- Databases published via Access Services can consume data from SharePoint, other Access DBs, SQL, and ODBC. This is more than enough data sources for most users.
- Nice Looking Forms
- Access utilizes HTML 5 and has some nice looking forms that take their cues from the Windows 8 interface. Developers can build in custom actions at the top of each form to execute macros and improve the end user experience with UI and data macros. Discrete custom actions buck the trend to place too many buttons on forms and lead to a confusing user experience, which can be found in many Access DBs.
- The days of manually massaging SharePoint data after exporting from Excel are over. While reporting in Access Services is done via the fat client app you can leverage data sources from all sorts of SharePoint sites and mold them into meaningful reports.
- I feel SharePoint developers lose people when they start talking about content types and site columns and that may intimidate people from starting to develop SharePoint solutions. Access Services doesn’t require knowledge of C#, .NET, or core SharePoint concepts. All you need to know is Access and have a SharePoint site to publish to.
- Easy to publish/App Store
- It’s super easy to save and publish Access apps to SharePoint. The Access fat-client has changed so users can only develop with what will publish to SharePoint. This means there is no compatibility checker, because the compatibility of working with SharePoint is guaranteed!
- Additionally, developers can save their app and redistribute it through their organization. This frees up developers’ time significantly. If a developer built an app form Team A and Team B wants to utilize a lot of the same functionality, Team B can download the App from the SharePoint App Store and make it their own, leaving the developer more time to focus on higher priority work. By sharing the app, we prevent the knowledge of the app from residing with a single resource.
- AKA The Functionality Formerly Known as Forms. Views are built using HTML5. They’re responsive and easier to develop than previous versions of Access. Built in the Access client via a WYSIWYG editor, they’re easy to build and customize.
- While the end user reaps the rewards of the macros, the Access client has handsome macro builders that allows easy drag and drop functionality to create powerful operations in no time.
- App Store
- With the App model in SharePoint, administrators ultimately have jurisdiction about what can and cannot be available in their environment.
- Look Ma, no SharePoint Designer!
- Access Services allows users to build composite applications without the need for SharePoint Designer. In environments where SPD use is prohibited or in the hands of a few, Access Services can empower end users to build their own apps and publish them to SharePoint, which in turn can drive collaboration and adoption of the platform.
- The biggest change to Access Services in SharePoint 13 is that all of the data in Access resides in SQL. Every app belongs in its own contained database. This means that you’re no longer stuck with the limitations of SharePoint when running your Access app, you’re only limited by SQL. SharePoint only renders the app’s HTML and CSS. Meaning all of the processing is done by SQL in what Microsoft calls "Access Run Time." This should assuage any fears of an Access App taking down a WFE.
Access may be 20 years old, but that doesn’t mean we should be working like its 1993. Access Services on SharePoint 13 takes the best parts of Access and SharePoint and provides 21st century solutions for users across the organization.