Ms Office Tune Up

In the business world, many of our documents are collaborative efforts that are created, edited, or commented upon by multiple people. This kind of teamwork is commonplace in Word, Excel, and PowerPoint, but it’s becoming increasingly the norm in Access, as well. The most common Access collaboration scenarios involve placing the database in a shared network folder or moving some or all of the data to a SharePoint site.

These intranet solutions are fine if you only have a few people who collaborate on the data. However, what if you need input from dozens or even hundreds of people? For example, you might need some data from each employee or input from a large number of product managers or department heads. In such cases, it doesn’t make sense to give each person permission to access the database. In the past, database administrators usually sent out queries to each person asking for the required data, and then a data entry user would input the responses into the database.

Fortunately, this costly and slow solution will soon be a thing of the past thanks to a new Access 2007 feature called Access Data Collection (ADC). With ADC, you create a form that includes fields for the data you want to collect, place that form in an HTML email message, and then send that message to every person from whom you want to collect the data. Each person fills in the form and returns the message, which is then saved in a special Outlook folder called Access Data Collection Replies. You then synchronize Access (by hand or automatically) with those replies, and the data is added to the underlying table.

CAUTION

Many people set up their email clients to read messages in plain text, and that’s not good for ADC. First, viewing the ADC message in plain text prevents the user from seeing the form at all. Second, even if the user converts the message to HTML (by clicking the Information bar and then clicking Display as HTML), Access perceives this as a “change” to the form, and it won’t process the reply. That is, in the Access Data Collection Replies folder, the Data Collection Status column for the reply says the following:
Failure: Cannot process this e-mail message. The form in this e-mail message is either corrupt or has been modified.
The user must turn off the Outlook option to read messages in plain text (choose Tools, Trust Center, click E-mail Security, and then click to deactivate the Read All Standard Mail in Plain Text check box), reply to and fill in the ADC form, and then reset the plain text option.

Creating the Access Data Collection Message

Best of all, creating an ADC form is a snap thanks to an Access wizard that builds the form step by step. Here’s how it works:

1. 1. In the Navigation pane, click the table you want to use to store the collected data.
2. 2. Choose External Data, Create E-mail. Access starts the ADC Wizard.
3. 3. Click Next. The wizard asks whether you want to use an HTML form or an InfoPath form.
4. 4. Click HTML Form and then click Next.
5. 5. If the table already contains data, the wizard asks whether you want to collect new information or update existing information. Click one of the following options and then click Next:
   • Collect New Information Only-Click this option to send a blank form for new data.
   • Update Existing Information-Click this option to send existing data for the recipient to edit. The record that contains the recipient’s address is the record the recipient edits.
6. 6. For each field you want to include in the form, click the field and then click > (or click >> to add all the fields). Click Next.
7. 7. If you want Access to automatically synchronize with Outlook when the replies arrive, click to activate the Automatically Process Replies and Add Data to Table check box (where Table is the name of the table you chose in step 1) and then click Next.
8. 8. Choose how you want to specify the message recipients (click Next after you make your choice):
   • Enter the E-mail Addresses in Microsoft Office Outlook-Click this option to enter the recipients by hand in the Outlook message window that displays later. Skip to Step 10.
   • Use the E-mail Addresses Stored in a Field in the Database-Click this option if you have the recipients’ addresses stored in the current database. Proceed to Step 9.
9. 9. Specify the addresses in the database using one of the following options (click Next when you are done):
   • The Current Table or Query-Click this option if the email addresses are stored in the table you use with ADC. Use the associated list to click the field that contains the addresses.
   • An Associated Table-Click this option if the addresses reside in another table that is related to the current table. First, use the associated list to click the field in the current table upon which the relationship is based. Second, when Access displays a list of fields in the related table, use the list to click the field that con­tains the email addresses.
10. 10. Edit the message Subject and Introduction, as needed. If the addresses came from the Access database, click where you want the addresses added: the To Field, Cc Field, or Bcc Field. Click Next.
11. 11. You now have two ways to proceed:
    • If you choose an Access field for the recipient addresses, click Next. Access dis­plays a list of the recipients with check boxes for each address. Leave the check boxes activated for the recipients you want to receive the message. When you are done, click Send.
    • If you specify recipients via Outlook, click Create to create the message, select the recipients, and then click Send.

Working with Access Data Collection Replies

If you receive an ADC message, you need to fill in the fields and return the message. Here are the steps to follow:

1. Click the Access Data Collection message and then click Reply. Access displays the message window.
2. Scroll down the message body until you see the form, as shown in Figure 1.

Figure 1 When you reply to an ADC message, fill in each form field in the body of the reply.

3. Click inside a form field and type the data.
4. Repeat Step 3 for each field.
5. After you fill in each field, click Send.

Finding Duplicate Records with a Query

As mentioned earlier, when you receive replies to your messages, they are automatically routed to the Access Data Collection Replies folder in Outlook. (This is a subfolder of the Inbox folder.) If you didn’t set up Access to automatically handle the replies, follow these steps to manually handle a reply:

1. In Outlook, open the reply.
2. Click Export to Access. Outlook asks you to confirm.
3. Click OK. Outlook exports the data.
4. Click OK.

Technorati Tags: access data via email

Popularity: 3% [?]