Add the data collected through e-mails to your Access database
You can collect or update information though e-mails by using Microsoft Access 2010 with Microsoft Outlook 2010. Access guides you through creating a data collection form and sending the form in an e-mail message. When your e-mail recipients return the completed forms, you can choose to have the collected data automatically processed and stored in a specified Access database. This method of data collection can save you time and effort that is usually involved in a copy and paste or a manual data entry process. The following are some scenarios where you can use this process to collect data:
Surveys : You can do a survey and compile the results by first creating an Access database with the necessary tables to store the results, and using the wizard to generate a survey form, and then mail them to the survey participants. When participants reply, the survey data is stored automatically in the database.
Status reports : Whether it is the latest inventory level status or the up-to-date information on pending issues, your team can keep you informed by sending you e-mail messages that contain the current information at regular intervals.
Event management : When organizing a conference or training or other events, you can send one or more forms as an e-mail message to gather contact information, travel and hotel preferences, and so on. If you choose to have the replies automatically processed, the participants are able to change their preferences at any time without having to notify you, and you always have access to the latest data for decision making purposes.
This article explains what you need to use this process, how to get started using the wizard, and then gives you additional information on various steps so that you can make the most informed selection as you follow the wizard
Note: You will not be able to add the collected data if your recipient used either Hotmail or Yahoo to send you the data collection form.
Before you start
If this is your first time collecting data by using e-mail messages, perform the following steps:
-
Ensure that you have installed the following applications on your computer:
-
Access 2007 or Access 2010.
-
Outlook 2007 or Outlook 2010. You must have Outlook installed and configured on the computer that you use to send the e-mail messages. If you have Outlook installed but not yet configured, start Outlook and follow the instructions in the Outlook Startup Wizard. For help with the wizard, see Outlook Help.
-
If you plan on using an InfoPath form, make sure that you have InfoPath 2007 or later edition installed and your recipients will also need to have InfoPath installed on their computers.
-
You e-mail recipients must have either have InfoPath or an e-mail client that supports HTML format installed on their computers in order to be able to view and edit the form.
The form used to collect data through e-mail messages is not an Access form. The wizard generates a special form in either HTML or InfoPath format.
Identify a destination database. The database can be in either .mdb or .accdb format. In addition, .mde and .accde files are supported. If you don't want to use an existing database, you must create a new one.
-
-
Identify or create the tables that you want populated with the data from the e-mail replies. If you are collecting data for existing tables, ensure that the tables are not read-only and that you have the necessary permissions to add to or update their contents.
Note: The Attachment, AutoNumber, OLE, and Multi-valued field types cannot be collected by using e-mail messages
If your data collection operation populates two or more tables, you must create a select query and use that as the form's record source. Ensure that the query includes all of the required fields from the underlying tables. If you are using an existing query, ensure that the query is not read-only and that you have the necessary permissions to add to or update its contents. For more information about creating a query based on two or more tables, see the article Introduction to queries.
Store the data as new records in the following situations:
-
If the data will populate multiple tables.
-
If the destination table does not have a primary key field. The primary key values are necessary to map each reply to an existing record. If your destination table does not have a primary key field, either add it now or assign an existing field that has unique values as a primary key.
-
If the destination table does not have any records. If the table is empty, the wizard assumes that you want to add records.
-
If the e-mail addresses of the recipients are not stored as a field in the database. When collecting data to update records, you will not be able to manually type the addresses in the wizard-generated message. The address field must be in the destination table, or in a table that has a relationship with the destination table.
Getting Started
-
Open the database in which the collected data will be stored.
-
To add the data to a single table, select the table in the Navigation Pane. To populate multiple related tables, select the query that is bound to these tables.
-
Do any one of the following to start the wizard:
-
On the External Data tab, in the Collect Data group, click Create E-mail.
-
Right-click the table or query, and then click Collect and Update Data via E-mail.
Note: If the table does not contain any fields, or only has the AutoNumber, OLE Object, Attachment, or multivalued Lookup field types, Access displays the following message: The selected table or query does not have any fields that support collecting data using e-mail.
-
If the table contains fields that support data collection, the wizard starts. If the destination table supports both the adding and the updating of data, the wizard guides you through the required steps to collect data through e-mail messages.
Use the following sections as a reference for any questions that you might have through the following seven major steps of the process.
Choose the type of data entry form that you want to send
Select the type of form based on the ease of use for yourself and your recipients.
Select HTML form An HTML form can be viewed and edited by any user whose e-mail client supports HTML
Select Microsoft InfoPath form You will see this option only If InfoPath is installed on your computer. Select this option only if your recipients all have InfoPath and Outlook installed on their computers. An InfoPath form offers a better data entry and editing environment,
The wizard page that is displayed next depends on whether the destination object supports the updating of data. If the object is a query based on two or more tables, or if it is a table that does not have a primary key field or does not contain any records, the wizard assumes that you want to add new records, and prompts you to select the form fields. In all other cases, the wizard prompts you to specify whether you want to add or update data before asking you to select the form fields.
Choose whether to collect new data or update existing data
Specify what you want to do with the data. The type of form that you are using affects the number of new records that a recipient can send to you in a single reply. When updating data, the number of records that must be updated by a recipient identifies the number of pre-completed forms that will be included in a message.
Note: To add new records, type the e-mail addresses in the address box of the e-mail message when the message is created. To update existing data, the e-mail addresses of the recipients must be stored as a field in the underlying table or query, or in a related table.
Specify the data you want collected
When choosing the fields to include in your form, it is not necessary to include every field that exists in the underlying table or query in the form. However, the following conditions must be met:
-
Required fields: Fields marked with an asterisk (*) symbol, are required fields. If you are collecting new records, the wizard will automatically include all fields whose Required property is set to Yes. If any record's required fields are blank, Access fails to add the record to the table.
-
Unsupported field types: You will not be able to include certain field types in the form such as, AutoNumber, Attachment, OLE Object, or multivalued lookup fields.
-
Primary key field: The user will be able to type a value in the field when adding records however; the reply will not be processed if the specified value is already in use.
Note: For each included field, under Field Properties, specify a label and click Read-only if you don't want the user to change the data in that field.
Specify automatic or manual data processing
You can select an automatic or manual processing of replies. Automatic processing means that when you get the replies, Outlook and Access work together to export the data to the destination tables in your database. Automatic processing can save you time and effort. Manual processing only means that you start the export operation to transfer the data from a specified folder in Outlook, to the destination table in Access.
Replies are successfully exported to the destination table as long as the following conditions are met at the time the replies reach your mailbox:
-
Outlook must already be running on your computer. If Outlook is not running, processing starts the next time that you start Outlook.
-
Access must not be password-protected, and should not be open in Exclusive mode. The name or location of the database must not have changed since you sent the e-mail message.
-
The names of the tables and queries, and the properties of the fields included in the form, must not have changed since you sent the e-mail message.
-
You must have the required permissions to add or update the contents of the underlying tables and query.
Note: If automatic processing fails, try fixing any issues, and then manually export the replies that failed. Any replies that reach your inbox after you resolve the issues continue to be processed automatically.
Set properties to control the automatic processing of replies
To specify a different Outlook folder where replies are stored, on the Specify how you want to process the replies page of the wizard, click the folder name. In the Select Folder dialog box, either select a different folder, or click New to create a new folder.
If you click Set properties to control the automatic processing of replies to change or fine tune import settings, see the following table for information on the available options:
| Option | Result |
| Discard replies from those to whom you did not send the message | Select this check box if you want only those replies that were sent by the original recipients of your message to be automatically processed. Replies from other people are stored in the destination folder, but are not automatically processed. |
| Accept multiple replies from each recipient | Select this check box if you want only the first reply from each recipient to be processed automatically. The second and subsequent replies are stored in the destination folder, but are not automatically processed. Note: This setting only controls the number of replies, and not the number of records within a single reply, that are processed. In other words, if you send an InfoPath form, a user is able to send you multiple records in a single reply, and Access automatically processes all of the records in the reply, even if this check box is not selected. |
| Only allow updates to existing data | When recipients use an InfoPath form to update data, they can send new records in addition to updates to existing records. Select this check box to process only the updates to existing records. |
| Number of replies to be processed | Type the total number of replies (from all recipients) that you want automatically processed. If you want all replies to be automatically processed, enter a large value, such as 5000, in the text box. Replies that are received after the specified value is reached are stored in the destination folder, but are not automatically processed. |
| Date and time to stop | Specify when automatic processing of replies should stop for this e-mail message. Replies received after this date and time are stored in the destination folder, but are not automatically processed. |
To change these options at a later date, on the External Data tab, in the Collect Data group, click Manage Replies. In the Manage Data Collection Messages dialog box, select the message for which you want to change the settings, and then click Message Options. The changes you make in the dialog box affect all subsequent replies that you receive for that message.
Manually processing replies
To control when and which replies are processed, do not select the Automatic process option. The replies will be stored in a specified folder in Outlook until you select and right-click each reply in the folder, and then click Export data to Microsoft Access in the shortcut menu.
In some instances, you might also have to manually process the replies that failed to be processed automatically.
Remember the following before exporting data
-
The Access database should not be open in exclusive mode, and the name or location of the database must not have changed since you sent the e-mail message.
-
The names of the tables and queries, and the properties of the fields included in the form, must not have changed since you sent the e-mail message.
-
You must have the required permissions to add or update the contents of the underlying tables and query.
Select how you want to specify e-mail addresses of the recipients
If you are collecting new information only, you can specify the e-mail addresses in two ways:
-
Type the e-mail addresses individually in the Outlook e-mail message, or select the addresses from an address book.
-
Use the e-mail address field in the table or query of the current Access database.
Note: When collecting data to update existing records, you do not see this dialog box, because the recipients' e-mail addresses must first be available as a field in the database.
Entering the e-mail addresses directly in Outlook
If you are specifying your recipients in Outlook, you can preview and customize the message before sending it. In the e-mail message body includes a brief introduction and a form. It is recommended that you do not make any changes to the form. Changes to the form structure might result in the reply not being processed.
Using the e-mail addresses stored in a field in the database
The option to select An Associated table refers to tables that are related. To view or edit table relationships in your database, on the Database Tools. tab, in the Relationships group, click Relationships
For more information about relationships, see the article Guide to table relationships.
Review and specify the list of recipients
The final major step involves previewing and customizing the e-mail message, fine tuning your recipient list, and then sending your data collection message.
Create and send the e-mail message
When the wizard informs you that you can now create the e-mail message, and shows you how to view e-mail status by using the Manage Replies command, some warning messages might appear. The following table describes each warning and the action you can take to resolve it:
| Warning message | Description | S olution |
| Some records do not contain a valid address in the specified e-mail address field. No data will be returned for these rows. | The e-mail address field that you selected contains null values. | If you want to collect data for every record, exit the wizard and replace the null values with e-mail addresses. Then start the wizard again. |
| You currently have an exclusive lock on the database; automatic processing will fail until the lock is released. | You have the database currently open in exclusive mode. | If you chose to have the replies automatically processed, processing fails because Access cannot add to or update a database that has an exclusive lock on it. Close and reopen the database in non-exclusive mode immediately after sending the message |
| These e-mail messages might contain data that is of a confidential or sensitive nature. | You are collecting data to update existing records, and the form that you are about to send will be pre-completed with existing data. | If some form fields include sensitive data, go back to the wizard step where you selected the fields and remove the fields that contain sensitive data from the form |
After you preview and customize the message, you can filter the e-mail address field and select your recipients. Choose the e-mail addresses that you want to use by selecting the corresponding check boxes. If you see a dialog box listing invalid e-mail addresses, make a note of the addresses in the list and click Exit. Verify the invalid addresses, make any necessary correction, and then try resending the message.
No comments:
Post a Comment