databasedev.co.uk - database solutions and downloads for microsoft access

Collect data by using e-mail messages in Microsoft Access 2007

This tutorial deals with a new feature introduced to Access 2007. Until now the possibility to retrieve information into your Access data base from infrequent users who are not connected to you application was limited to several options. So far the easiest way – but also quite long – was to explore the data base as Excel file and then send it as attachment to the persons who needed to fill the information and when you get the info, someone would have to put it in the data base...long process not to mention that a lot of errors might occur.

The new Access 2007 feature allows you to gather information in the email form based on two main approaches: HTML or InfoPath. The recipients need to allow HTML messages or have InfoPath installed (which is a part of the MS Office Professional suite).

To make it easy to understand how the data collection feature works, this tutorial will use one of the templates which come with Access 2007. You might need to download a temple, which can be easily done in the following way.

Click on Sample and click on Northwind 2007 then on Download.

Fig 1 Download the template

When you download a temple in any MS Office application for the first time, a genuine validation process will check if you have a genuine copy of the software. After you click Continue it will start the download process.

Fig 2 After the genuine validation process, Access 2007 will download the template

By default Access 2007 is protected and doesn't allow macros to run. So click on Options (marked in Fig 3) then choose "Enable this content" and click OK in order to be able to use the sample database.

Fig 3. Enable the macros to use the data base sample

Let’s assume Northwind just hired a new employee and you need the data base to be update with the employee’s information but you are not the kind of PR manager to constantly interact with your employees (which is quite normal in big companies). So you need to send them an email and retrieve the data.

In order to do that, go to the left of the screen and click on Supporting Objects, right click Employees and then choose "Collect and update data via e-mail"

Fig 4 Start the collect data wizard

The wizard will walk you through the required steps. You need to choose the type of data entry. For this tutorial we have chosen HTML.

Fig. 5 Choose the type of data

Then you need to tell Access 2007 if you need to collect new information or update old ones. We’ll be collecting new information. Now specify what data the new employee needs to fill in. Move the fields from the left part to the right part (the right part will contain the info the employee needs to fill in).

Fig 6 Choose what data you need to collect

Now you can tell access to automatically update the Data base when information comes in the mailbox. Click "Set properties…" and you can choose a stop date for the process.

Fig 7 Tell Access to automatically add the data to the database and when to stop looking for new email

Now you can either use an Access 2007 data base with the emails or you can manually enter then. We’ll manually enter the email since we decided to send it to only one new employee. It’s time to customize the email and then click Next. In the next dialogue you might be told that the data base is open in a restrictive mode. You need to close/reopen the data base to be able to send the email. Of course then follow the steps above to get to this point. Now click on Create and an email message will open allowing you to add the employee’s email.

Fig 8 Add the employee’s email and send it. Notice the form in the email

Click on Send and OPEN Outlook 2007. Otherwise the email won’t be sent. You will also be asked if you want to create a folder where Outlook will save the replies. Once the answers arrive you can use the Access Button in Outlook 2007 to import all data.