Monday, August 2, 2010

Access Services in SharePoint 2010

By Laura Rogers

There is a new feature in SharePoint 2010 called Access Services, but the integration story between SharePoint and Access goes way back to SharePoint and Office 2003. Since SharePoint 2003, there has been the ability to link SharePoint lists and libraries to Access so that they are represented as tables in a database. Queries and relationships can be created, as well as nice-looking reports. This method of reporting has been used by small companies that do not have more high-end services.

By using SharePoint as a Web front end to an Access database, users who are inputting data do not need to have Access on their computers, and they do not need to take turns opening a database file. But the problem with this method is that the database still needs to be stored in a file share for the people who run the reports.

In SharePoint and Access 2007, some new integration capabilities were added. One of these features is a new type of view, called an Access view, that can be created for any list or library. People who have Access installed on their computers when creating a new view will see this view type option.

In Access views, create any type of object, such as Tables, Queries, Forms or Reports. Then, when this view is published to SharePoint as a view, there will be a new item with the name of that Access item in the Views drop-down box. Any other people who have Access installed on their computers can view those Access objects.

With SharePoint and Access 2010, you get all of the great capabilities that existed in 2007, and more. SharePoint lists and libraries can still be connected and used inside of databases, but with the Enterprise version of the product, Access Services can be used. With Access Services, the entire Access database can be published to SharePoint as its own entire sub-site. The tables in the database become SharePoint lists, the forms become Web-based, and even the business logic in the database is translated to become workflows in SharePoint.

Once the database is published to Access Services, the original database file can be thrown away because it is not referenced anymore. Note that there is a sometimes painstaking process of going through the Web compatibility checker before a DB can be published, but this process completely depends on the complexity of the database.

All in all, if your company's departments have various databases floating around, they will all have the ability to put them in SharePoint 2010 and take advantage of Access Services. Also, smaller companies can still use Access as the reporting tool for data that is in SharePoint, even if they do not own the Enterprise version of the product.

Laura Rogers is a consultant with SharePoint911.

No comments: