If you’ve built SharePoint solutions with many-to-many relationships in SharePoint 2007 you may have come to appreciate the boundaries that existed when dealing with relational data. You may have instead decided to leverage back-end relational databases to manage your table structures rather than utilising native SharePoint lists. With the introduction of Access Services in the SharePoint 2010 Enterprise version we now have the ability to create, share and manage relational databases online within our SharePoint sites.
In the past we may have tried to bend SharePoint to fit with our relational data needs. For example, via a combination of SharePoint lists, web pages containing list view and/or dataview web parts, or InfoPath forms. Alternatively, we’ve made the decision to store our relational data in a separate back-end database and expose this data on our SharePoint site through the Business Data Catalog (BDC). Both of these approaches can be time consuming to deliver what should be relatively simple business solutions.
In SharePoint 2010 Business Connectivity Services (BCS) continues to deliver the functionality provided by the BDC however we also have the added advantage of features offered through Access Services.
As the name implies, Access Services is a Microsoft Access Web database that has been published to the web. This offers us the best of both worlds:
- We now have relational database capabilities within our SharePoint environment that allows us to quickly generate relational databases, forms and reports; and
- We can now create/publish, centrally manage and share user-defined Access databases and components online.
If we take an Access Database and publish it to SharePoint the key Access components will be mapped as follows:
| Access Database Component |
SharePoint Component |
| Forms |
.aspx pages |
| Tables |
Tables |
| Macros |
Workflows (or in some cases JavaScripts) |
To help us get started with Access Services in SharePoint 2010 Microsoft have provided a number of:
- Site Templates, for example: Contacts Web Database template, Assets Web Database template
- Application Parts - such as tables, queries, forms, reports which can be built once and re-used in other Access Services database applications - for example: user management, custom error reporting
- Quick Start fields - which can be combined / bundled up and re-used in other Access Services database applications, for example: address fields (street address, postal address, city, post code), name fields (first name, middle name, surname, full name)
With SharePoint 2010 we have the opportunity to rethink how and where we manage our relational data that we want to expose through SharePoint. In the case of relatively simple many-to-many databases where we want a quick turn-around to build and deliver the business solution we may decide to leverage Access Services. Where we have complex high volume relational data we may decide to continue to deliver this through separate back-end databases.
Another factor to take into account is whether we’re dealing with departmental or team data versus corporate controlled data. Corporate controlled data is often best served up through specialist applications with corporate scale back-end databases. Where we identify a need to surface corporate controlled data (and other data stored in back-end databases) within our SharePoint sites we have the opportunity of leverage the BCS and Enterprise Search capabilities of SharePoint 2010.