Access 2007 has a nifty feature that allows you to collect data via email. Recently, I got the following question from a user:
Is there any way to automate the creation, sending and receiving of Emails through the Data Collection capability of A07 using VBA? The wizard is too complex for the users I am working with.
Here is the reply we got from the developer—it isn’t for the faint at heart…
There isn’t a straight-forward way to do this and it isn’t officially supported. It’s possible if you don’t mind a little reverse engineering… The key is that there are two places Data Collection uses to do its work. One is in a system table inside Access used to store information about the action, and the other is an XML file that Outlook uses when mails are received to identify Data Collection mails and associated databases.
The system table is called MSysDataCollection. There’s one entry for each data collection action that’s created in the database. A quick rundown of the fields:
Active – Controls whether the current Data Collection action should be run.
BasedOnType – This marks whether the data collection is based on a query or a table
CreatedDate – When was the data collection action originally started.
ExternalID – A unique GUID to identify the Data Collection action
FormName – Name of the form; appears in the e-mail subject
InfoPathForm – True if we sent out an InfoPath form, false if it was HTML
Mapping – An XML chunk that defines how fields in the table/query map to fields in the form. Your best bet is to create a few Data Collection actions similar to ones your users might create,
Office 2010 Home And Stude/nt Key, and mimic the XML. Here’s some of the interesting bits <formProperties> stores most of the properties found in the Options dialog of the Data Collection Wizard <tables>, despite the name, only supports one table. This section stores the database column name to the form field name mapping. <table>’s name attribute should always be “table1”, accessTable is the table name inside Access, and collectionType is either “insert” or “update” <iterate> points to where in the InfoPath/HTML form we’re looking for data (as opposed to formatting, etc.). This doesn’t change much. There will be one <mapitem> for each column in your form, including hidden fields. formNode is for the form, and is always “table1fieldX”, tableCell is for the Access table The <recipients> tag stores who we sent the mail to, in case we want to only accept certain people’s responses, or limit how many times,
Office Professional 2007, or what rows,
microsoft Office 2010 keygen, they can update.
OutlookFolder – This is the Outlook item ID of the Outlook folder replies should be moved to when processed.
SentDate – The last time this data collection item was mailed out (updated through the Resend feature).
The other bit is the XML file for Outlook, and lives in <AppData>\Microsoft\Access. It’s called AccessDCActionFile.xml. There’s only one file for a given user on a machine, and each Data Collection action gets added to the same file. Again, the easiest way to understand the file is to create a few actions and see what we write out, but here’s some highlights:
<outlookFolders> - Stores the Outlook ID for the default Data Collection folders in Outlook.
<mdbMap> - Each data collection action gets one <form> tag under here.
<form> - id is the GUID that matches the External ID in MSysDataCollection, moveToFolder matches OutlookFolder,
Office Professional Plus 2007 Key, and FormType is “HTML” or “InfoPath”
<path> - path to the database
<userReply> - This tracks whether the user who sent the Data Collection e-mails has replied to it themselves.
<formProperties> - Pretty much a copy of what’s in the system table’s Mapping section. By having it here,
Windows 7 Activation, we don’t have to grab it from Access every time we need it.
<lastShutdown> - The last time Outlook was shut down.
Lastly, you have to actually generate the HTML or InfoPath form. We do this by manually building the HTML or XML for the forms. I’d recommend cracking open some Data Collection forms, looking for the nodes and GUIDs stored in the HTML or XML, and recreating them that way. I don’t have a more convenient way to do this, and it’s probably the hardest part to get right.
With this info in hand, you can do the follow to create a Data Collection action through automation: Collect whatever information from the user you need (recipients, options, etc.) Add a row to MSysDataCollection Add a <form> entry to AccessDCActionFile.xml Create the HTML/XML mail. Use Outlook’s OM to send the e-mails.
The client version of Outlook does need to run in order to receive and process e-mails, but this will get the data collection action created and running. <div
Windows 7 Activation Automate data collection form
Hybrid Mode
