Integrate SharePoint Online with Access 2010

By Ken Withee, Jennifer Reed

One of the scenarios you may encounter, as the number of site collections for your organization grows, is the need to aggregate data from various lists to glean business intelligence and run reports on. SharePoint Online’s integration with Microsoft Access 2010 (Access) allows a non-programmer to work around current SharePoint Online limitations to solve this problem.

Or, if you are a growing small business with an existing web database in Access, you can leverage SharePoint Online to allow members of your organization whose subscription plans do not include Office Professional 2010 to interact with your database through the browser.

MS Access 2010 is bundled with Office 2010 and comes with an array of web database templates compatible with the Publish to Access Services feature. Office 365 subscription plans include Access Services, a feature that lets you publish any of those Access templates to your SharePoint sites.

In very simplistic terms, an Access database is a relational database that organizes your data in tables. The tables in the database are comprised of rows or records and the attributes of those records are defined in the columns or fields. The database becomes relational when attributes from the table form a relationship or dependency on another table.

image0.jpg

For example, you can have a Contacts table that contains the contact information for all your customers. You can then have a separate Orders table that contains the order history of all your customers in the Contacts table.

The beauty of tracking all this data in separate tables rather than one long flat list is not lost on Access users who catapulted the software to fame when Microsoft shipped its millionth copy of MS Access 1.1 in September 1993, just ten months after announcing its release.