Access has blown me away

19 10 2007

For nearly a year and a half I have had Office 2007 installed on my laptop in one form or another. I love the functionality of the big four applications, Outlook, Word, Excel and PowerPoint and use them extensively during my working week. Every now and again I need to use Access to run a custom reporting tool our PMO lives by. Yesterday during some down time I had a poke around Access and what I found blew me away. Access has had a fair dose of SharePoint love in 2007 and I have to say it rocks.

I downloaded and installed the Project template project (well I am a PM after all), it allows you to set up projects and tasks as you would expect. The functionality is impressive, but not really up to the standard that say MS Project Server or Primavera would give you. I doubt seriously you could run and track a project with it. However what really impressed me were two features:

SharePoint List Data Sources

Data can be stored in a SharePoint list. Whilst information can be stored within the Access database, you can also link it into existing SharePoint lists and store data there. In fact you could design an application that has both a web front end (SharePoint) and a rich client (Access) using a single database stored in WSS. Wow.


Automated Data Collection

Now the part that really got me was the out of the box workflow integration with Outlook. 


In the main screen on the application, if you click on Collect Data a wizard starts up that allows you to define a number of fields that you want to collect updated data for:


You have the option of two mechanisms to collect data, either via straight HTML email which provides an impressive but simplistic data collection mechanism, or via an InfoPath form that provides a richer experience.

You also have the option of choosing to collect new data, or only allowing existing data to be updated


Finally and here is the really clever part, the wizard allows you to configure the database to automatically process the replies and add it to the database.


There are a few more steps you have to go through and voila the emails are sent out. The screen shot below shows one of the InfoPath versions of the email including combo boxes and date pickers for a richer experience for the end user.


Now the really clever part happens when you submit or reply to the response. A new system folder is automatically added to your Outlook 2007 client called ‘Access Data Collection Replies’ all responses to the data collection emails will be directed back to that folder.


When the response comes in, it is automatically parsed and an update made to the database! If it doesn’t work, it flags it. Wow!!


This technology rocks, you can have a pretty simplistic workflow solution up and running using nothing more than Access & Outlook. There is no need to write custom ASP pages, no web services, no stored procs, the stuff just works. I can certainly think of a couple of scenarios at work that would benefit from this kind of solution.



%d bloggers like this: