My First Prediction for the New Year: Why Access Services Will Be a Big Hit in 2010

As we prepare to close out 2009 it is normal for people to start looking forward and make predictions about things in 2010. Here at Microsoft, 2010 will be another huge release year for us with new versions of SharePoint, Office, Visio and Project scheduled to be released in the first half of 2010.

While there are many things to be excited about in the 2010 release, one area that I see tremendous value for both business and IT folks is the release of Access Services as a server side capability within SharePoint 2010.

Many of you know that my background includes having spent about 14 years as a customer working at large and small companies as an IT architect, developer, director and various other roles. During that time I constantly had a love/hate relationship with Microsoft Access.

Since it is so easy for people to create, copy and share Access databases, the tool took off and spread like wild fire. This is a mixed blessing and double edged sword. On the plus side, most Access solutions are not built by traditional developers which is great at addressing some applications that the IT team does not have resources available to work on or they would be too expensive to build from scratch. A negative to this approach though is the number of different Access databases that have popped up and how challenging it can be for IT to manage, track and secure all of these mini applications.

From an end user perspective, Access continues to be a great tool for Rapid Application Development (RAD). With minimal training and effort, a non developer can quickly build sophisticated solutions using Access that join data from multiple data sources (e.g. SQL Server, Oracle, SharePoint lists, Excel workbooks, flat files, etc.) and that data can be mashed up into nice reports, queries and data entry forms.

From an IT perspective, this power comes with some interesting challenges. For example, Access was never designed to support a large number of concurrent users (i.e. multiple people working in the same database at the same time). There is also the challenge of Access database sprawl, security and compliance. Then there are also the concerns around support, maintenance and upgrades – especially over the years when the original “developers” of the Access solution move into new roles (inside or outside of the company) and the business is now being run on this Access application.

SharePoint and Access 2007 took some small steps in the right direction to addressing some of these concerns. For example, you can now move Access database content to SharePoint 2007 lists and have the data stored inside of SharePoint where it can be properly managed, secured, backed up and audited. End users can take this data offline with the Access client and continue to perform reporting and querying against this data and other data coming from outside of SharePoint. This was a good start.

SharePoint and Access 2010 make huge improvements in areas such as the following:

1. SharePoint 2010 lists have been beefed up to scale to 50 million items in a single list and have added governance and throttling controls to make sure IT can manage how large lists can get, how many items should be in a view, etc. In short, it allows you to have large amounts of data stored in SharePoint while making sure that an end user does not bring the server to its knees by doing something they shouldn’t.
2. SharePoint 2010 lists now add relational database concepts such as enforcing unique constraints (make sure duplicate rows are not inserted), adding relationships between lists (don’t delete a record from a child list if a related record is on the parent list or delete records from the child list when it’s parent record is deleted) and adding validations (field length must be greater than x number of characters).
3. Business Connectivity Services (BCS) in SharePoint 2010 allows you to centrally create and manage read/write connections to external databases, including CRM, ERP and other Line of Business (LOB) systems. The great thing about BCS connections is that once the connection is created, it can be reused by other SharePoint and Office client applications without every person needing to know the database server name, database instance name and user ID and password required to connect to the data source. In some ways, you can think of a BCS connection as a server side ODBC connection on steroids. :-)
4. Access Services allows you to take advantage of these major list and BCS improvements in SharePoint 2010 to publish Access applications (not just data) from Access 2010 which now run inside of a web browser (including Internet Explorer, Firefox or Safari).
5. Standard Access features like reports, queries, datasheets and macros can be published to SharePoint 2010 and run in the browser with no additional client applications installed. This is not just a read only view – the data can be updated and maintained as well via the browser. Access reports that run in the browser are built on top of SQL Server reporting services and support the ability to export to Word, Excel and PDF formats from within the browser.
6. The rich Access client application can still be used to manage and publish changes to the Access application and serves as the offline client for cases where you want to take the data offline and work with it. When you reconnect, Access will synchronize and merge your changes with other changes done server side including helping to deal with conflict resolution when multiple people update the same record at the same time.
7. UI and data macros in Access 2010 enable you to develop a single application that can run both as a local rich client application and a browser based server side application. The macros are created in Access 2010 using a new macro designer that includes Intellisense. The macros are both created and managed as XML. Many of these macros create SharePoint workflows under the covers to build upon that platform and perform similar to database triggers.
8. Access Services adds additional client and server caching capabilities on top of what SharePoint 2010 offers to add even greater scalability for these applications – above and beyond what a standard SharePoint list could provide. Only the changes (deltas) are synchronized between the client and server when you work with an offline copy of the Access web database.
9. Access Services are just one example of how SharePoint 2010 enables the usage of Composite applications (aka mash-ups) that can be quickly created and deployed while still enabling the proper level of support, governance, security, auditing, retention policies and management.
10. While Access Services is a great next step there are still some limitations to what you can run as an Access web database in SharePoint versus what can be done in a local Access database. Access 2010 includes a built in compatibility checker that is used at design time to ensure cross platform support before deployment. There are also different Access database formats supported in the 2010 release to enable backwards compatibility with prior Access client applications.

Still looking for more information on Access Services, including a demo and how to test this out yourself? Check out the related blog posts on the Access team's blog.

UPDATE 2/16/2010: New Access Services Whitepaper has been published.