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.
Chapter 23, “Sharing Web Databases with SharePoint Server 2010,” of my forthcoming Microsoft Access In Depth book covers Web Databases, the official name of Access 2010 projects published to SharePoint’s Access Services. Web Databases are a totally revamped version of the Data Access Pages (DAP) feature offered by Access 2003 and earlier. (Access 2007 and 2010 don’t support creating DAP.)
DAP were intended for intranet (private) access only and were very difficult to adapt to secure Internet deployment. If you you have a Web-facing SharePoint Server (SPS) 2010 Enterprise edition, you can take advantage of the SPS security infrastructure for Web Database user authentication and authorization.
If you don’t have an on-premises SPS 2010 Enterprise farm, you can publish Web Databases to hosted SPS instances. Chapter 23 includes a section with the details for publishing an Access *.accdb database to AccessHosting.com’s multi-tenanted SPS service “in the cloud.” AccessHosting (@accesshosting) offers a free 30-day trial of their service.