Combining AS/400 Data with Microsoft Word

Q: We're trying to transfer an AS/400 data file to a Microsoft Access file. We've tried a few different transfer methods but can't get the file into Access format. We want to use the mail merge functionality between Access and Microsoft Word to print a Word document using variable data generated by AS/400 application programs. Is there another method to accomplish this? Our goal is to print a PC-based document that includes logos and graphics combined with data from the AS/400.

A: Although you can use an Access file as an intermediate storage location for data you want to print in a Word document, there's really no need to do so. Word's mail merge feature can work directly with data that's stored on an AS/400. You don't need to transfer the data into Access first.

To use Word's built-in mail merge with the AS/400, you first need an AS/400 ODBC driver such as IBM's Client Access ODBC driver, which is supplied as a part of Client Access Express or Client Access for Windows 95/NT. You should also use the ODBC Administrator to create a new data source using the ODBC driver that specifies the names of the AS/400 system and library where the data files reside. In addition, you must have Microsoft Query, an optional part of Microsoft Office, installed on your PC.

To create a new Word mail merge document that links to the AS/400, perform these steps:

a. Select the Mail Merge option from Word's Tools menu to display the Mail Merge Helper dialog box.

b. Click the Create, Form Letter, and Active Window options from the Mail Merge Helper menu. The mail merge toolbar will be added to the Word document.

c. Click the Get Data and Open Data Source options to display the Open Data Source dialog box.

d. Click MS Query (this button is displayed only if MS Query is installed on your system) to start Query and display the Choose Data Source dialog box.

e. Scroll through the list of data source names, select the name of the data source you created using the ODBC Administrator, and click OK. (If you're prompted with a sign-on to AS/400 dialog box, enter your OS/400 user ID and password.) Doing so displays the Add Tables dialog box.

f. Scroll through the list of tables and select the tables that contain the field you want to include in the mail merge. As you select each table, a graphical representation of the table is added to Query's Query pane. When you've finished selecting tables, click the Close button to close the Add Tables dialog box.

g. Select the fields you want to include in the mail merge by dragging and dropping each field from Query's Query pane to the Grid pane. (You can select all the fields simply by double-clicking the asterisk displayed at the top of the fields list.)

h. After you've selected your fields, click File and then Return Data to Microsoft Word from the MS Query menu. (If you're prompted with a sign-on to AS/400 dialog box, enter your OS/400 user ID and password.) A message box will appear stating "Word found no merge fields in your main document."

i. Click the message box's Edit Main Document button to display Word and enable the Insert Merge Field drop-down menu in the mail merge toolbar.

j. Type the text and insert the graphics where you want them to appear in your Word document.

k. Position your cursor at the location where you want to add each merge field, select the Insert Merge Field drop-down menu, and choose the desired fields from the list. The selected fields will appear in the document surrounded by right and left brackets (<< >>).

l. When you finish creating the document, click the Merge icon on the mail merge toolbar to display the Merge dialog box.

m. Click the Merge button on the Merge dialog box to display the new document containing all the merged AS/400 data.

n. Click Word's Print icon to print the document.

The above tip is from a NEWS/400 Tech Corner item by Mike Otey.

[report a broken link by clicking here]