MS Word mail merge using MS Access LIKE operator

Mail merge refer to the process of using/linking data from an external source to fill out an otherwise static document. An example can be a formal letter that send to all board members. The content of the letter is the same (static), but the title/address-block and greeting can be pulled from a database so that letters are with proper greeting and title of the addressed board member.

For most part, Microsoft's (MS) word processing (Word) and databbase (Access) works well with each other. However, under one situation, the normal way of linking MS Word and MS Access failed to work correctly. This article is to report a work around for that situation.

In general, database can hold a lot of information and it is not uncommon that a mail merge incident only need a small part of those information stored in the database. For example, the database may hold the contact information for all employee, while the letter is addressed only to board members. In database, queries are used to filtered out unwanted information and MS Word can use queries to obtained the needed information. For most of queries, MS Word worked well with MS Access.

The Problem:
When a query uses the LIKE operator/keyword, the normal way of linking MS Word and Access could fail. The Word will return results that would be produced with the '=' operator instead of the 'LIKE' operator. This problem had been reported dated back to 2005 and still exist today (Aug. 2011) in Word 2007.

The work around:
To work around the problem, we can have Word linked to Access through an ODBC (Open Database Connectivity), which defines a standard protocol to be implemented by middle-layer software to allow applications (e.g. Word) to connect to various database.

To proceed, in Windows environment, you proceed by defining a DSN (Data Source Name) through the ODBC manager ( -Start -Control Panel -Administrative Tools -Data Sources) with 'Driver do Microsoft Access' as the middle-layer software and the Access database file (.mdb) as the database file.

Once the DSN is in place, you can now use it as the mail merge data source. In Word, when prompt to select the data source, click the 'New Source...' button, Select ODBC DSN and click Next. Select the DSN you created, click Next, select the query using the LIKE keyword, click Next and you are prompt to save these settings in a data connection file to be used later. Click 'Finish'.

Now you can highlight the connection file and click open. The query should work correctly.