In this article you will learn the fundamentals of publishing an Access 2010 database application to the Web by using Windows Server 2008 R2 and SharePoint Server 2010.
If you have deployed SharePoint Server 2010 with the Enterprise license to your organization, then you can purchase the Office Web Apps (OWA) add-on to provide your SharePoint portal users with browser-based editions of Word, Excel, PowerPoint, and OneNote.
In case you wondered, “But what about Access?” don’t fret. Access Services is included with the SharePoint 2010 Enterprise license. The coolest thing about Access Services and OWA is that you can host Microsoft Office documents in your SharePoint portal and your users don’t have to have the full-blown Office applications installed on their computers; the only requirements on the client side are (a) a Web browser; and (b) access to the SharePoint portal.
By the end of this brief article you will understand how to publish Access 2010 databases to your SharePoint portal with a minimum of muss, fuss, or greasy aftertaste.
The first thing we need to do to get Access Services on its feet is to ensure that the Access Services service application is installed in SharePoint, and that the related server service is running.
Log into SharePoint Central Administration and click Manage service applications from the Application Management category.
SharePoint 2010 Central Administration
If you performed a default installation of SharePoint Server, then you should find a service application entry for Access Services. Ensure that the runtime status is listed as Started.
Verifying the Access Services Service Application
If for some reason you don’t have Access Services installed, you can open the New menu and select Access Services from the service application list.
Adding Access Services to SharePoint 2010
Next, from the Central Administration home page click Manage services on server from the System Settings group (the link is shown in Figure 1). Find Access Database Service, and ensure that its status is Started.
Verifying the running state of Access Services
The final back-end configuration step involves logging into your production SharePoint portal site and verifying that the SharePoint Server Enterprise Site Features are running.
From the front page of your portal, open the Site Actions menu and select Site Settings.
The SharePoint 2010 Site Actions menu
From the Site Settings page, click Manage site features from the Site Actions group.
The SharePoint 2010 Site Settings page
Finally, in the feature list, location SharePoint Server Enterprise Site features and ensure that its status is Active.
Verifying the SharePoint enterprise feature set
Now that we have SharePoint Server prepared to host an Access 2010 database application, let’s fire up the Access 2010 client and get to publishing!
Publishing a Web Database
When you start Access 2010 and navigate to the Backstage View, you’ll notice two types of database templates available: standard databases and Web databases. The chief difference between the two template types is that the Web database templates are pre-set for compatibility with Access Services.
You’ll find that there exists a number of limitations and “gotchas” with respect to Access 2010 database compatibility with SharePoint. Thus, you may be best off my creating your database by using a built-in or downloaded Web database template, and then migrating your production Access database data into the Web database shell.
The Backstage view in Access 2010
In this example, we created a Web database by using the built-in Contacts template.
The Contacts Web database
The good news, regardless of whether you want to publish your existing Access database or one of the built-in Web databases to SharePoint, is that Access 2010 includes a compatibility checker utility. Simply open your target database, navigate to the Backstage View, select Publish to Access Services under File Types, and then click Run Compatibility Checker.
Publishing a Web database to Access Services
If your database does not pass the compatibility check (a highly likely scenario if you use one of your existing, non-Web databases), then the interface turns red and you can click Web Compatibility Issues to view a report of problems found.
Failing the Web Compatibility check
In my opinion, Access does a great job of listing the compatibility problems in the Web Compatibility Issues table it generates. Work to resolve the problems and rerun the Compatibility Checker until the file passes.
The Web Compatibility report
Once your database is “greenlighted” by the compatibility checker tool, you will see the The database is compatible with the Web message and you can complete the rest of the form.
Provide the server Uniform Resource Locator (URL) to your target SharePoint portal, and give a site name. Be sure not to include spaces in your site name; Access Services will host your database in a subsite of the given SharePoint portal.
Publishing to SharePoint 2010
If all goes well during the publication process, you will see the Publish Succeeded dialog box, as shown in Figure 14. Click the indicated URL to visit the new Access Services subsite.
Figure 14: Confirmation of Access Services publication
Using the Access Database in SharePoint
The hard work you exerted earlier in the process in forcing your database to pass the compatibility checker should pay off handsomely when you observe how faithfully Access Services renders your database in a Web browser. It’s pretty impressive stuff, for sure.
The Access Services Web site
The Access Services site template functions a bit differently from most of the SharePoint 2010 site templates. Instead of a Site Actions menu, we have an Options menu that we can use to customize site behavior, edit the underlying source file, or navigate up to the parent site.
Access Services Web site options
At this point you have the fundamentals under your belt such that you should be able to verify the running state of Access Services in SharePoint 2010 and publish an Access 2010 database to your portal. Please be sure to leave your questions and/or observations in the comments area of this post; I’m happy to help.
For further study
- Set Up and Configure Access Services
- The Access Show: Access 2010 demo of Access Services and web databases
- Access Services in SharePoint 2010 – All You Need to Know
- Thoughts on Access Services in SharePoint 2010
- Access 2010 Web Databases: How Can I Put My Access Database On The Web?
- Access 2010 Web Databases: Web Compatibility
- Access 2010 Web Databases: Creating Relationships