Automate data collection forms using VBA
Access
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, and mimic the XML. Here’s some of the interesting bits
Leave a Reply