Merging DB2/400 Data with a Microsoft Word Document

by Shannon O'Donnell

Something that users often ask for is the capability to put their AS/400 data into their Microsoft Word documents. If you are not sure how to accomplish this feat, then you may be a bit uncomfortable when these requests come along. But fear not, dear reader, for the solution to this problem is actually quite easy to achieve.

Mail Merge

To add AS/400 data to any Microsoft Office application, such as Word, Access, or Excel, you have several choices. This article will look at using the mail merge feature in Word. Mail merge will allow us to define a merge document, define what fields we want to display on it, then print and display the new document, with the AS/400 data included.

The Pieces

Here's what you need to create your mail merge document in Word:

• An ODBC data source to your AS/400
• Word 97 or higher
• Microsoft Query installed (comes standard with Office 97 and higher, although you may need to manually install it from the installation CD)

ODBC

The first step is to configure an ODBC connection to your AS/400. This is amazingly simple. First, if you have not already done so, install Client Access Express (any version) on your PC. This will install the ODBC drivers you need at the same time.

Next, click the Windows Start, Programs, IBM AS/400 Client Access Express, and ODBC Administration menu items. Once you do, you'll see a figure similar to the one shown in Figure 1.

Figure 1: The ODBC administrator panel adds, deletes, and edits ODBC data sources

Click the Add button, and you'll see a panel like the one shown in Figure 2. Click the Client Access ODBC Driver (32-bit) list item, then click the Finish button.

Figure 2: Add a new ODBC data source from this panel

The last step is to select an already defined AS/400 system connection and to give your ODBC data source a name (see Figure 3). We'll call ours OS400-Data-Source.

Figure 3: Don't forget to name your ODBC data source

That's it; you're done with defining ODBC for this AS/400. Told you it was simple!

Creating the Mail Merge Document

Now let's create the mail merge document in Word. Start Word and open a brand new document. Click the Tools menu item, then Mail Merge. You'll see a panel like the one shown in Figure 4.

Figure 4: Create a mail merge from this panel

Click the Create button and select Form Letter. Next, choose to add the merge fields to Active or a New document. Now we have to define the Query to retrieve our data. To do so, click the Get Data button, under the Data Source header, click the Create New button that will appear, then click the MS Query button, shown in Figure 5. This will bring up the list of ODBC data sources.

Figure 5: Select the MS Query Button to start the ODBC query

You should find your OS400-Data-Source data source in this list (see Figure 6). Remember we created this in the previous step? Select this data source.

Figure 6: Choose the previously defined data source name

Now select a database file on your AS/400, then select the fields from that file that you want to appear in your Word document. I used a file named CSTMST in library QGPL. You should use whatever database fields are appropriate for your documents (Figure 7).

Figure 7: Select the data fields that will appear in your document

Adding Data to Your Document

Now you need to add some text to your document and tell Word where you want to insert your ODBC data. You tell Word where to insert the merge fields by positioning the cursor where you want the data field to go and then clicking the Insert Merge Field button, on the mail merge toolbar. If you don't see that toolbar, click Word's View menu, select Toolbars, then Mail Merge.

When you're done, your Word document should look similar to this:

To:  «FIRST_NAME» «LAST_NAME»
        «ADDRESS_1»
        «CITY»,  «STATE»    «ZIP» - «ZIP4»
        «E_MAIL»



Dear Sir,

Your family pet, Fluffy, has once again found 
its way into your neighbor Mr. Gruffly's yard. 
Mr. Gruffly has retained the services of our 
firm to contact you about this matter and to 
demand that you herewith and forthwith cause 
said Fluffy to cease and desist all destructive 
behavior in Mr. Gruffly's yard. Failure to do 
so will result in further stern warnings and 
the conversion of dear Fluffy's hide into a 
lovely fur smoking jacket.

Respectfully,

Thaddeus L.  Awyer

Merging the Data

That's it! To merge the data, click the Merge menu item, on the Mail Merge toolbar. Your AS/400 data will now be merged into the document! Nifty, huh? Of course, you can get a whole lot fancier with all of this than I've shown you here. You could sort the data coming in, print actual mailing labels with AS/400 data on them, use a bit of Visual Basic for Applications to prompt the user for selection criteria, and on and on. But this article gives you the foundation you need to get started. I leave it to you to build on what you've learned here.

Sponsored By ASNA

Why Roto-Rooter Uses ASNA Visual RPG for Application Development Roto-Rooter knew that the time had come to replace their green-screen technician dispatch system. When they selected AVR as their development environment, they realized they would be able to use their existing RPG programming skills. They also found that they were able to program even faster than with traditional RPG. Their new AVR application includes capabilities not possible in the green-screen app, such as graphical representations of technician schedules, easy to access menus, and tabs that allow easy movement from screen to screen. These capabilities make their new application much easier for their users to learn and use than the old green-screen version. Since the application now features more information on the screen at one time, in a format that is easier to read, users are able to see and correct scheduling conflicts more quickly so they can provide a higher level of customer service. "AVR has saved us so much time while working on our first visual application. I would say that we are able to cut our programming time, just for subfiles, in half and we use subfiles in over 90% of the programs that we have created for this project. It's really nice to be able to move our employees from dumb terminals to PCs and get them using more useful and user friendly applications." --Tom Duebber Download your FREE copy of ASNA Visual RPG today! http://www.asna.com/AVR_literature.asp