the Access Experts: Custom Database Solutions

Home
Services
Products
Links
Support
Portfolio
About Us

 

Managing Merges with Access

 The mail merge capability of Microsoft Word is a powerful tool that can be used to automate much of the drudgery of creating form letters, mailing labels and catalog-style documents.  It’s also a great tool for creating reports with complex formatting, and sending personalized e-mail to individuals without resorting to some of the more sophisticated (and expensive) email tools that are designed for that explicit task.  However when the number of merge records gets large, data sources using Word, Outlook or Excel are often quite slow and cumbersome to use.  On the other hand, Access can handle the task with relative ease – IF it all works correctly.  There-in lies the rub, especially if you want the merge task to be handled by a non-technical person.

In this tutorial we want to share some useful tips from our experience in using merges with production databases.

Data Source Challenges

When you rename an object that is used as the merge data source (a common occurrence as a production database evolves), Word promptly announces it can’t find it’s data source and gives you the choice of  finding the data source or dropping the data source information.  It is often difficult to retrieve the name of the data source Word thinks it is supposed to use.  Word displays it in the Mail Merge Helper dialog box, but if the path to the database is of any substantial length, you can’t read the data source table or query name. 

Furthermore, if you move or rename the database, when Word tries to start the database, another common occurrence, it finds it can’t, and then gives you two options: remove the data source information, or remove all merge information.  At that point, the solution is to move and/or rename the database back to it’s original state, write a small macro to display the data source name, and then repeat the move/rename process and specify the new data source.  Along these lines, an annoyance related to Word is that if you try to change the data source, it does not remember that you were using an Access database previously, nor does it remember the path.

In some cases, especially if Access security is being used, a merge initiated from Word always starts a new instance of Access, even if Access was already open with the correct data source database.  We discovered that a common cause was that the application name had been changed to something other than Microsoft Access.  We do this frequently so that users can identify the application correctly.  The solution to this problem is to rename the application to Microsoft Access – XXXXXX.

Other Merge Issues

Another issue that users commonly face is the need to use a parameter query as the data source for a merge.  In that situation, you must use DDE as the method of connection to the Access database.  In Word 2002, that can be a substantial challenge under some conditions, as the option isn’t always presented, depending on configuration issues.  In addition, DDE presents some performance problems, so you may want to explore other options in creating a document.

Hans Vogelaar (of Woody's Lounge fame) suggests an alternative where you use a form to collect the parameter information.  When the user has completed the information, VBA statements are used to construct the SQL string with the actual criteria embedded in in the string.  The merge is then completed using an ODBC connection, which provides significant advantages in both speed and reliability.  This does imply that you need to be reasonably proficient with VBA, and with creating queries dynamically by manipulating the QueryDef collection.  One detail should be noted: you must specify the SubType argument of MailMerge.OpenDataSource as wdMergeSubTypeWord2000 or an error will occur.

Another situation that occurs is corrupt main merge documents.  We have seen documents, which have been used successfully for some time, suddenly either loose all of the data source information, or actually crash Word when you try to open it.  OLE Automation, now commonly referred to as Automation, is one way to tame the problems with merges, and also makes user instructions for production databases simpler.  There are still some complications that need to be noted.

In many cases, especially if you are using Access security, Word will still attempt to open a second instance of Access to attach to it’s data source, even if you have set the Word data source in your automation code.  Also, Word invariably prompts you about saving the main document after you have completed the merge and close down Word.  The natural inclination of most users is to say yes, which generally doesn’t cause any harm, but will on rare occasions cause document corruption.  The solution to this problem us to actually close the main merge document in code after executing the merge, so that the user is not prompted, and to deliberately remove the data source information from the main document and then save it as a read-only document.  In our tutorial on automation, you will find a sample set of code for executing merges to Word from Access.

Merging to email

The experience with Word-based email messages has generally positive.  In this case, the user selects the data source when the document is converted to a mail merge document.  Then they specify the destination of the merge to be Electronic Mail using the mail merge dialog box or with the Mail Merge Wizard in Word 2002.  One requirement is that the main document must contain at least one merge field before Word will let you execute the merge – this caused some problems at some clients.  The solution was to use a merge field that we knew would always be empty.  Another issue that confronts email merges, is that Word/Outlook insists on saving a copy of every email sent in the Sent Items folder, even if Outlook is configured to not do so. 

Finally, the problem of frequently changing email addresses can cause serious logistical problems.  One option is to develop a tool to identify rejected email addresses and mark them as bad when emails are returned as undeliverable.  If you are considering this approach, be sure to design your system so that you can mark rejected addresses as bad and exclude them from future merges.

Alternatives to Merges

Other methods for producing some types of merges should be considered, especially if there are a large number of data records, say 1000 or more.  The first thing to be considered is whether an Access report would be acceptable.  Reports are much quicker to generate and to print, and can have many of the same characteristics as a Word document.  On the other hand, you are limited in what you can do with formatting of characters, paragraphs and so forth with reports, even if you choose to use a rich text control.  Also, customized reports can’t really be emailed in the same fashion as a Word merge to email.

Another method of creating a complex report that is effective, especially for creating a single copy of a document, is to use Automation.  In that situation, you program Word to create a document, or create it from a template, and do the normal formatting you would do if you were building the document manually.  In one situation, we encountered the need to do complex formatting in the header and footer of the document, and to change at various points in the document.  It had originally been created as a catalog merge in Word; rewriting it as a Word automation document built the same 600 page document in about one fifth the time and nicely supported the header and footer customizations.

Summary

In summary, if you are contemplating Word mail merges driven by Access on a recurring or production basis, consider these points:

  • Use OLE Automation code to drive the Word merge from Access
  • Save the main document with no data source and make it read-only
  • If you change the application name, make sure it starts with Microsoft Access
  • If several merge documents exist, store the document names and data sources in Access tables so the information is preserved and readily available
  • If you are merging to email, be sure your Outlook (or Exchange Server) folders have room to store the twice the number of emails you will send.
  • If you are using Office XP, be sure to read how to invoke the DDE method.

Further Resources

 

Copyright © 1998-2011 Wendell Bell & Associates Inc.  Last updated on August 1, 2011