i5/OS and Microsoft Office Integration Handbook

by Chris Peters, Brian Singleton

Overview

In this, the third edition of the perennial best-seller, iSeries gurus Chris Peters and Brian Singleton have completely updated this essential resource to reflect all of the latest changes and enhancements of iSeries Access and Express Client and Microsoft Office. Now, you can use iSeries Access or Express Client to combine the presentation capabilities of all versions of Microsoft Office with the database capabilities of the i5/iSeries to provide your company with the best of both worlds.

Tasks that used to be almost impossible are now an easy reality...if you know the integration secrets revealed in this book. Discover how to use your iSeries output with PC data-formatting tools, make visually appealing reports using iSeries data, use visual query tools to point and click to create sophisticated information output, analyze and summarize the detailed and often cumbersome iSeries reports, tap into the power of Microsoft's powerful Universal Data Access using ADO.NET, ODBC, and OLE DB, and much more.

Singleton and Peters provide you with the essential knowledge you need to use iSeries Access or Express Access as a means to integrate iSeries or AS/400 data with Microsoft Office applications. i5/OS and Microsoft Office Integration Handbook, Third Edition covers installing and configuring iSeries Access or Express Client, using ODBC to seamlessly integrate iSeries with Microsoft Office, and leveraging the iSeries Access data transfer function. The book also explains how to bring data from the iSeries to your PC by using NetServer, TCP/IP's FTP file transfer function, and/or Universal Data Access with OLE DB and ADO.NET.

Other chapters of the book explore the multitudeof Microsoft Office functions by devoting a specific chapter to each Office application to show you how to use each function with the i5/iSeries. You'll learn how to use Excel, Word, Query, Access, and Outlook to generate spreadsheets, mailing labels, form letters, and envelopes; to produce queries and perform data retrieval; to create beautiful, sophisticated reports; and much more.

If you have PCs attached to your i5/iSeries this book has the essential information that will allow you to harness the power of Microsoft Office and exploit the iSeries database.

This book will show you how to:
• Create spreadsheets, reports, and graphs using Microsoft Excel and iSeries data
• Merge iSeries data to create letters, labels, and envelopes in MS Word
• Install and configure iSeries Access or Express Client for Microsoft Office integrationSort, filter, join, summarize, and total iSeries data with MS Query and MS Access
• Transfer data between PCs and the iSeries and automate the process
• Set up Outlook as an email client on an iSeries or AS/400 server
• Configure Network Neighborhood to access your i5/iSeries and make sure your files are secure
• Create VBA macros for iSeries data integration with MS Office applications
• Integrate iSeries data into .NET applications with ADO.NET

Contents:
INTRODUCTION
Chapter 1: Connectivity Overview
Chapter 2: ODBC
Chapter 3: Retrieving iSeries Data with Microsoft Query
Chapter 4: Using iSeries Data with MS Word
Chapter 5: Analyzing DB2/400 Data with Excel
Chapter 6: Expanding your Options with Microsoft Access
Chapter 7: The iSeries, eMail, and Outlook
Chapter 8: The iSeries in the Network Neighborhood
Chapter 9: The iSeries Access Data-Transfer Function
Chapter 10: Transferring Data with FTP
Chapter 11: Microsoft OLE DB/ADO Technology
Appendix
Index

Product Details

ISBN-13:	9781583477373
Publisher:	Mc Press
Publication date:	05/01/2012
Sold by:	Barnes & Noble
Format:	eBook
Pages:	450
File size:	9 MB

About the Author

Chris Peters is the author of The AS/400 TCP/IP Handbook and is a frequent contributor to MC Mag Online as a columnist for Microsoft Office integration. He lives in Nine Mile Falls, Washington. Brian Singleton is the author of The AS/400 & Microsoft Office Integration Handbook. He lives in La Jolla, California.

Read an Excerpt

The i5/OS and Microsoft Office Integration Handbook

---

By Chris Peters, Brian Singleton

MC Press

Copyright © 2005 MC Press Online, LP
All rights reserved.
ISBN: 978-1-58347-737-3

---

CHAPTER 1

CONNECTIVITY OVERVIEW

Interaction between two computers, like an iSeries system and a PC, requires a coordination of efforts. The two systems, each operating in its own time and for its own purpose, must work together at several levels. When systems like these are to communicate, there will typically be the following:

* Some hardware involved, to provide a physical communications link

* Software to interact with the specific communications hardware at the lowest level

* Basic utilities to allow interaction with data and programs on the systems

* At the highest level, application programs to interface with users and create comprehensive computing systems

This book deals with the two highest-level aspects of communication: utilities and application systems. Both of these come into play in developing solutions in a cooperative iSeries and Windows PC environment.

The commercial software packages involved in this book are Microsoft Office and IBM iSeries Access. These are the systems that facilitate the communications and the applications for most iSeries and iSeries business concerns. These packages also furnish the most common benefit.

IBM ISERIES ACCESS AND EXPRESS CLIENT

Before you can access data residing on your iSeries and use it in a PC -based application, you must have a connection between the iSeries and a client PC. Physically, the connection will be some form of TCP/IP networking, like Ethernet, Token Ring, or Point-to-Point Protocol. Once a physical connection has been established, there has to be some communication software that provides data and services over the network. For any application except generic utilities like FTP and Telnet, the communications software developed specifically by IBM for the iSeries will be required.

iSeries Access (also called Express Client or iSeries Access, depending on the version) is an IBM package of communications software written for the iSeries and PCs that provides low-level data exchange and communication services. A variety of communication services come with iSeries Access, like ODBC and ADO. It pays to take some time to get to know your options within iSeries Access, and what you can do to maximize convenience and productivity when exchanging data between Microsoft Office and the iSeries.

ISERIES ACCESS COMPONENTS

iSeries Access is a collection of tools and components that perform different functions to help PCs and the iSeries interact. Several of these components are useful for moving data between an iSeries and a PC. The following list ranks some of the more prominent iSeries Access components in what we think is their importance in connecting to Office applications:

* ODBC. ODBC is part of the iSeries Access's base support, and it is one of the primary methods used by Office applications to retrieve data from external data sources such as the iSeries. Figure 1.1 shows the ODBC Configuration Program. (Chapter 2 provides detailed information on ODBC.)

* OLE DB/ADO. As Microsoft's follow-on technology to ODBC, OLE DB/ADO (ActiveX Data Object) provides a contemporary ActiveX interface to data and processing. It also improves performance in certain applications on a variety of platforms, including the iSeries.

* Data-Transfer Function. The data-transfer function shown in Figure 1.2 (formerly known as the file-transfer function) is another useful method for retrieving data from the iSeries for Office applications. While not quite as flexible as ODBC or OLE DB/ADO, it is a solution that stands by itself; no additional programs are required to use it for bringing iSeries data to your PC. It can also be easily automated, and it has a specific function for bringing data into Microsoft Excel. (Chapter 9 provides more information on the data-transfer function.)

* NetServer. The iSeries can participate in a Windows network at a peer level using an i5/OS technology called NetServer. NetServer lets you access your iSeries system as if it were just another Windows server on your network. While this might not be the speediest solution, it has a lot to offer in terms of convenience. (Chapter 8 provides more information on NetServer and the network-drive function.)

* File Transfer Protocol. If your PCs are connected to the iSeries over a TCP/IP communications link (such as Ethernet, Token -Ring, or PPP), you may use the iSeries native support for standard FTP. Similar to the iSeries Access data-transfer function, FTP is a standard service available within iSeries TCP/IP utilities for transferring data or program objects and executing iSeries-side commands. (Common utilities like FTP and Telnet are part of base TCP/IP support, not part of iSeries Access or Express Client.)

NetServer, the data-transfer function, FTP, ODBC, and OLE DB/ADO are the main iSeries Access components for actually transferring data between the iSeries and a PC. The following additional components are useful in other circumstances, such as performing configuration operations:

* PC5250. The terminal emulator included with iSeries Access is called PC5250. As shown in Figure 1.3, it is the portal to the familiar iSeries green screen. As such, it is probably the most frequently used component of iSeries Access.

* The iSeries Navigator. The iSeries Navigator, shown in Figure 1.4, is an excellent tool for managing many different aspects of your iSeries. It gives the iSeries a PC-based graphical user interface and can greatly ease such common tasks as managing users, setting system and resource security, and configuring system options. (Several components within iSeries Access can be configured from the iSeries Navigator only; they have no 5250 command-line equivalents.)

ISERIES ACCESS LICENSES

IBM provides some iSeries Access functionality as part of the base i5/OS operating system, without requiring the purchase of an iSeries Access license. This can have a great effect on the tools you use to retrieve data from Office applications. Figure 1.5 shows a dialog box displayed during the installation of iSeries Access or Express Client. It details which components require a license and which don't, within a particular release of iSeries Access or Express Client.

The main iSeries Access components used for iSeries and Office data integration are OLE DB/ADO and ODBC. Fortunately, both of these are no-charge options. Unless IBM's license agreements have changed since this book was published, you should be able to install and use the iSeries Access ODBC driver and OLE DB/ADO support without worrying that you are in violation of a license agreement.

Another tool that falls into the "no-license" category is iSeries Navigator. As mentioned previously, this tool is useful for interacting with the iSeries using a GUI interface.

INSTALLING MICROSOFT OFFICE INTEGRATION

Installing iSeries Access is much like installing any other Windows application. The setup program leads you through several screens to determine which aspects of iSeries Access you want to install, as shown in Figure 1.6.

Your configuration of iSeries Access will depend on your particular version, but at a minimum you should locate and install the following:

* iSeries Navigator

* ODBC

* OLE DB/ADO provider support

* The iSeries Access Toolkit

CONFIGURING A CONNECTION

Configuring a connection from a PC to an iSeries is easy. Assuming the physical connection between your PC and the iSeries is in place, you only have to know the IP address or host name of your iSeries.

Many iSeries networks are configured to use a regular text name like "S101901R" to identify their iSeries, instead of an IP address. This method generally works well and has some advantages over using the straight IP address, but there is also a downside. Ultimately, all TCP/IP communication is done by IP address, not by host name. Therefore, any text host name must first be cross-referenced to an actual IP address by one method or another, which can sometimes be a source of problems.

Once you have a valid IP address or host name for your iSeries, the usual next step is to configure a PC5250 session on your PC. This is an easy process and will also serve to confirm your physical connection and IP address. Click the Windows Start button, and locate "Start or Configure Session," depending on your version of iSeries Access, under "Emulator" (or the equivalent). Select the option and follow the on-screen instructions. You'll be asked to specify the IP address or host name of your iSeries, which you acquired earlier. You might also be asked to supply the name of the workstation that the iSeries will use to identify your PC. If so, you'll have to find an unused name, or ask your iSeries network administrator for one. In most installations, the iSeries will automatically configure the iSeries line and device descriptions required to form a green-screen connection, and you'll be greeted with a sign-on display.

Creating a connection from your PC to the iSeries in this manner will dynamically update iSeries Access on your PC with the iSeries connection information. This information will be used by other iSeries Access tools (iSeries Navigator, for example).

In the course of getting your PC set up for iSeries Access, you may specify that a default user ID and password be used when starting communications services. You might want to give this some thought. If you do not specify a default user ID and password, certain automated solutions might pause in midstream and ask the user to manually key the information. On the other hand, storing a user ID and password to be automatically used might violate your security policy, or restrict access to unauthorized objects in the iSeries.

More information regarding setting up iSeries Access can be found at IBM's Information Center, http://publib.boulder.ibm.com/pubs/html/as400/infocenter.htm, or at the iSeries Access Family Library, http://www-1.ibm.com/servers/eserver/iseries/access/calib.htm.

CHAPTER 2

ODBC

ODBC (Open Database Connectivity) is a specification, or agreement, about how data that resides in a given database is made universally accessible to applications. Most ODBC arrangements involve several participants, including an ODBC server program running on the host, an ODBC user program running on a client, and an ODBC driver that provides the details about how to get to a specific database. A number of ODBC drivers are available for the iSeries; here, we will use the ODBC driver that comes with iSeries Access for Windows.

Microsoft Office makes extensive use of ODBC to connect to the iSeries and retrieve data. This chapter shows you how to configure the iSeries Access ODBC driver for use with Office. It also covers the security issues involved with client/server technologies like ODBC, and provides some iSeries-specific ODBC tips.

WHAT IS ODBC?

ODBC is a data-access standard created by Microsoft and other vendors to allow application programs to access data from any type of database that supports the standard. It's also one of IBM's preferred means of client/server connectivity for the iSeries. Combined support from industry giants such as IBM and Microsoft ensures that ODBC will be a perennial standard for database access, so it is worth your while to learn how it works.

With step-by-step instructions, this chapter explains how to configure IBM's iSeries Access for Windows ODBC driver. Before you begin, however, you need some background information on ODBC.

WHY USE ODBC?

Since IBM already has a function for transferring data to clients, why include ODBC? ODBC by itself does not transfer data to clients. It is merely a tool to enable the transfer of that data. Client programs are required to take the data from the iSeries and put it into a format useful for the PC. In contrast, the data-transfer function will, by itself, transfer data from the iSeries to your PC's hard drive. Both methods are useful in different circumstances. Use the data transfer function in the following cases:

* Offline data access is required or desired.

* No ODBC client utilities are available to perform the transfer.

* ODBC security is a concern.

Use ODBC in these cases:

* Up-to-date information is required.

* The data is too big to exist on the PC.

* The data-transfer program does not provide the necessary functionality.

In addition, if you are doing application development, ODBC is better than the file-transfer function. While the file-transfer function performs static file transfers, ODBC can dynamically update records on the iSeries in real time.

As explained in later chapters of this book, many client applications work directly with ODBC databases. This seamlessly enables you to take advantage of the functionality of those applications, without having to go to an external program to retrieve the data. This can be a real time-saver, improving ease of use and providing the capability to take advantage of the client functionality. For example, a large number of third-party tools are ODBC-enabled, allowing them to work directly with iSeries data in ways that you might not have imagined.

ARCHITECTURE

ODBC consists of three layers:

* Driver manager

* ODBC driver

* Data sources

These layers function together as shown in Figure 2.1 to bring the data to your application.

The driver manager is a Microsoft utility that manages the individual ODBC drivers installed on your system. It provides the interface between the applications and the drivers, allows for the management of data sources, and handles some of the application programming functionality.

ODBC drivers are the programs that translate between the external data source and the ODBC programming interface. There are specific drivers for many different data sources, including local PC database files and remote databases such as DB2 on the iSeries.

The iSeries ODBC driver from IBM is supplied as a free component of iSeries Access. Other ODBC drivers, designed to interface with other types of databases, will likely be installed on your PC as well. Each of these drivers differs slightly in capability, performance, and configuration, but all offer remote database access for ODBC-compliant application programs such as Office. You can tell which ODBC drivers you have installed on your system by looking at the list of available drivers in the ODBC administrator program. To do this, start the Windows ODBC configuration program (Program Files/Administrative Tools/Data Sources (ODBC) in Windows XP). Then, click the Drivers tab, shown in Figure 2.2.

If your iSeries Access or Client Access driver is not listed, install the Data Access portion of iSeries Access from your iSeries Access/Client Access media.

The final participant in an ODBC system is a configuration record called a data source. This is the configuration record that you deal with most often when retrieving data from the iSeries. Data sources are named definitions of how ODBC will connect to a database. When they are named, they are given the acronym DSN (Data Source Name).

DSNs are created from ODBC drivers to refer to a specific database or library on the iSeries. ODBC drivers can have many different settings, such as default libraries and connection methods. When you create a DSN with the ODBC administrator program, these settings are stored with that data source. Therefore, the next time you want to access your data, you don't have to again enter the configuration information for the driver.

Under ODBC, there are three different types of DSNs in the 32-bit ODBC environment, and each type allows different access:

* User DSNs

* System DSNs

* File DSNs

As shown in Figure 2.2, a set of tabs at the top of the ODBC driver -manager screen allows you to access the appropriate type. Keep the following in mind when working with DSNs:

* User DSNs are available only to the current user and others who have authorized access to that user's objects.

* System DSNs are available to the operating system and anyone who is using the system.

* File DSNs are available to anyone using the system with the proper drivers installed. File DSNs also tend to work better with versions of Office prior to Office 2000.

For security purposes, user DSNs are helpful, for instance, if you need to create a specific DSN for a specific user, and you do not want other users of the machine to be able to see the DSN.

(Continues...)

---

Excerpted from The i5/OS and Microsoft Office Integration Handbook by Chris Peters, Brian Singleton. Copyright © 2005 MC Press Online, LP. Excerpted by permission of MC Press.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

Contents

Title Page,
Copyright Page,
INTRODUCTION,
1 - CONNECTIVITY OVERVIEW,
2 - ODBC,
3 - RETRIEVING ISERIES DATA WITH MICROSOFT QUERY,
4 - USING ISERIES DATA WITH MS WORD,
5 - ANALYZING DB2/400 DATA WITH EXCEL,
6 - EXPANDING YOUR OPTIONS WITH MICROSOFT ACCESS,
7 - THE ISERIES, EMAIL, AND OUTLOOK,
8 - THE ISERIES IN THE NETWORK NEIGHBORHOOD,
9 - THE ISERIES ACCESS DATA-TRANSFER FUNCTION,
10 - TRANSFERRING DATA WITH FTP,
11 - MICROSOFT'S OLE DB/ADO TECHNOLOGY,
APPENDIX - SUPPORT AND TROUBLESHOOTING,