14 October, 2006

Data tracking with SharePoint 2007 and Access 2007

Microsoft Office Access used to be quite a popular application for tracking almost any type of data as it was relatively easy to create a few tables, design some basic forms and off you went with a tool for tracking issues, tasks, requests, etc. However, it was always a problem to decide where to store the .mdf file so it could be shared and backed up properly.

There is a much improved solution for this in Office 2007. For any application based around data tracking and where offline support is a requirement, SharePoint 2007 and Access 2007 plays together very well. Access can be bound to SharePoint lists. This gives you the server-based benefits of SharePoint and provides you with a rich UI and offline support.

To start exploring this functionality try the following:
  1. Create an Issues Tracking list in SharePoint.
  2. Add some sample data to it.
  3. From the Actions menu, select Open with Microsoft Office Access.
  4. An Open in Microsoft Office Access dialog pops up. Make sure Link to data on the SharePoint site is selected and click OK.
A new Access database will be created complete with a number of reports, views and forms to add and edit issues. The data is stored only in the SharePoint list on the server and whenever you hit Save, changes you make in Access are saved back to the server. When you hit the Refresh button on the Home ribbon, changes made on the server are updated in Access.

The data is still linked to the SharePoint list. To take the data offline click Take All Offline on the External Data ribbon. You now have a local copy of the issues list including a rich interface to interact with it. The data is saved locally only.

At any point in time, you can hit the Synchronize button on the External Data ribbon which will synchronise the data with the SharePoint list on the server. If there are any conflicts, a Resolve Conflicts dialog will allow you to resolve them.

All in all, SharePoint and Access together provide a powerful and simple solution for data tracking. SharePoint enables proper sharing and backup of the data and Access provides a rich UI and offline support.

1 comment:

alistair said...

great article - I linked to it on my blog!