The nature of PC client/server programming using AS/400 data sources is changing. Whereas PC-to-AS/400 programming has traditionally focused on using ODBC within PC programs, IBM has opened up a second option with its AS/400 SDK for ActiveX and OLE DB toolkit—usually referred to as just the SDK. (For more information on comparing OLE DB against ODBC client/server programming with an AS/400, see “The ODBC and OLE DB Strategy Guide” elsewhere in this issue.) The SDK provides a quick, effective means of designing PC-based applications that communicate with an AS/400. While you can use the SDK to retrieve AS/400 data from such diverse products as INPRISE Delphi, Sybase PowerBuilder, Lotus Notes, and Lotus SmartSuite, the SDK is particularly well-suited for using Visual Basic (VB) or VBScript code to retrieve AS/400 data into Microsoft applications, including Microsoft Office.

In this article, I review how you can use IBM’s SDK for ActiveX and OLE DB to retrieve AS/400 data into Microsoft VB code. I discuss the reasons for loading the SDK, why you would want to use the SDK to create VB-to-AS/400 code, and the new VB wizards the SDK provides to automatically create client/server code inside of the VB editor. For detailed requirements and installation instructions, check out the “SDK Differences Between the Windows 95/NT Client and Express Client” sidebar in this article. After reading the information I present here, you’ll be able to easily develop VB applications that process AS/400 data in no time.

Why Use the SDK?

I like the SDK because it allows me to create client/server applications quickly with limited knowledge of VB. Some things, such as word processing and spreadsheets, are just easier to do on a PC. Features such as cut, paste, and copy are also far friendlier in a PC application. The SDK offers the best of both worlds by allowing the business logic and data to be kept on the AS/400 while the presentation logic resides on the PC.

There are many other options available for this type of integration, such as Java, but VB and the SDK are, I believe, the easiest way to get your feet wet in client/server programming. The big advantage VB has over Java is that it was designed to interface with the entire Microsoft Office family of products. Using VB, you can develop applications that

import AS/400 data directly into products like Microsoft Excel, Word, or Access. The first application I developed using the SDK is a program that reads an Excel spreadsheet, determines the field sizes, creates the corresponding physical file on the AS/400, and uploads the data. Try doing that quickly in Java!

Firing Up the SDK with Visual Basic

For the purposes of this article, assume you are working with the SDK included with the Windows 95/NT client (although most of these same instructions also hold true for the Client Access Express for Windows—the Express client—version). Once the SDK is downloaded and installed on your PC, you can begin developing client/server applications. Before using the Visual Basic wizards to create your AS/400 download, you must tell your VB applications which OLE DB objects they should use in the download. Do this by starting your VB editor (I used VB 5.0 for this article) and then clicking on Projects/References from the drop-down menu. You then need to select the Microsoft ActiveX Data Objects library (msado10.dll or msado15.dll) and the AS/400 Express Toolkit Table Index library (cwbzzidx.dll) as your project references. These references are the Component Object Model (COM) objects the SDK will use in creating your code.

To access the SDK to build your client/server code, click on the Add-Ins dropdown item on the Visual Basic editor menu bar. You’ll notice a new option has been added to this menu, AS/400 SDK, which displays nine new VB functions (see Figure 1). These options, known as the Visual Basic wizards, provide all the necessary tools you need to link to your AS/400 data directly from within a VB program.

To retrieve AS/400 information, the SDK uses the AS/400’s Distributed Data Management (DDM) server to provide record-level access to AS/400 data. In addition, it can use stored SQL procedures to transfer multiple AS/400 records to your application with one statement. There are pros and cons associated with each method. SQL stored procedures are faster, but DDM requires less work to implement because there are no stored procedures to write. The proper method to use really depends on the scope of the application being developed. (For another look at when you would use each method, see “Slam-dunking AS/400 Data into Microsoft Excel with OLE DB,” MC, May 1999.)

Checking Out Your Options

The first AS/400 SDK menu option to explore in Figure 1 is Options. This SDK feature allows you to configure which AS/400 libraries will be accessed via the VB wizards. When you select Options, a list of connections appears. Select the appropriate AS/400 connection and click on the Properties button. A Properties screen appears that allows you to add libraries that can be accessed by the wizards.

The next options to consider are the linking options, which provide the means for linking to AS/400 data in a variety of ways. First, take a look at the Link Tables option. (Again, select this item off the AS/400 SDK menu.) The term Tables is PC nomenclature for database files. When using the SDK, linking a table is a fairly straightforward process. Once you have selected the Link Tables option, a dialog box appears, asking if you want to add the DA400links.cls to your project. This class file contains the source code necessary for performing the various functions of the SDK, so select Yes to move on to the next step. A Link to Tables dialog box (shown in Figure 2) appears for selecting the tables to be linked to. If the table you want is not specified here, select the Add option to bring up the Add a Table Link window. The Add a Table Link window displays a treelike structure that contains the AS/400 libraries that have been selected via Options. On this window, you can select the database file you want to use, specify the Table Operations the VB code will be able to perform on it (e.g., Insert, Update, or Delete), and add it to your Links to Table list by following the on-screen instructions.

When you return to the Link to Tables list, select the file to be linked. The code needed to access the file is automatically added to the DA400links.cls file.

The next linking option is Link Stored Procedures. Stored procedures are programs that are written in any of the languages available on the AS/400 and then cataloged. SDK can then execute these procedures on the AS/400. Before they can be executed, however, they must first be linked to. Using stored procedures to provide data access requires more work because a separate AS/400 program needs to be written. However, if large amounts of data need to be accessed, a stored procedure is the best option. For manipulating remote data, stored procedures provide superior performance over an application written on the PC. Security is actually increased because the logic is contained on the AS/400. Performance is also better because communications overhead is reduced. Linking to a stored procedure is the same as linking to a table with one significant difference: You can use parameters. The Link Stored Procedures wizard (accessed by selecting the Link Stored Procedures option) prompts you to name any parameters that need to be sent to or received from the AS/400 program.

Another powerful feature of the SDK is its ability to link a VB program to an AS/400 data queue. You can link to a data queue for either sending or receiving data from an AS/400 object. Access the Link Data Queues wizard by clicking on Link Data Queues from the AS/400 SDK menu.

AS/400 commands can also be linked to and executed from your VB program when you select the Link Commands option from the AS/400 SDK menu; making this selection starts up the Link Commands wizard. The commands are coded in the VB application, but they are executed on the AS/400. It is important to note that only commands that can be executed in batch mode can be executed via the SDK. Interactive commands such as Work with Documents (WRKDOC), for example, are not supported. (You can use the SDK for interactive commands that support outfiles such as WRKACTJOB.)

The final AS/400 SDK menu is Link Programs. Linking a program provides the means for executing an AS/400 program directly from a VB program. The SDK supports the passing of up to 40 parameters between VB and the AS/400.

Time for the Fun Stuff!

Now that I have covered how to establish the connections, it’s time to discuss how to do something useful with them. The first area to be covered is creating forms from links. There are five different forms that you can create with the SDK. The options are grid, list, list box, form, and combo box.

The first form option is the grid. Creating a grid is quite simple. The first step is to create a link to a table on your AS/400 as discussed previously. Next, from the Add-Ins drop-down menu, select AS/400 SDK and select Create Forms from Links. This step produces a dialog box that contains the table you have previously linked to. Clicking on the control type box produces a listing of the types of forms that you can create (see Figure 3). After clicking the Next button, give your form a name, and you are finished. All that you have to do now is click the Run button, and your AS/400 file now appears on the screen as if it were an Excel spreadsheet. The form options available work best with small files because the SDK loads the entire file before displaying it, which can be a time-consuming process with large files. When you process large files, in most cases, SQL calls are a better option. You can use SQL to retrieve a selected number of records at a time instead of the entire file, which improves performance greatly.

The next forms option available is the list. As with the rest of the form controls, creating a list is the same as creating a grid, the only difference being the selection of the list control. A list looks similar to a grid, except it does not have the grid lines.

Next up is the list box. List boxes are small text display boxes with up and down arrows that are used for scrolling through data. You can also create combo boxes with the SDK. These controls create a text input box along with a drop-down box you use for selecting the desired option. Combo boxes come in handy for restricting the value entered in a text box.

The final option available is used to create forms. This, in my opinion, is one of the slickest features of the SDK. By using this feature, you can have a ready-to-use form created in minutes. After you select the form control type and link to a table, an additional dialog box appears (see Figure 4). This dialog box lists all of the fields in the database file you have linked to, allowing you to control how they will be displayed on the form. The three choices are text, label, and none. If you select none, the field will not be displayed on the form. Choosing text causes the field to appear as a text box. Using the label control makes the field act as a description. This option is great for creating a quick display screen if all you need is to display some data at a workstation.

Will Surf for Information

What I have presented here is just the tip of the iceberg of what you can do with the SDK. There are literally thousands of Web sites that contain information on coding VB applications. With a little creativity and ingenuity, you can create client/server applications that you never dreamed you could. So take the leap into this brave new world of client/server programming.

References

• A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support (SG24-5183-00), available for download in PDF format at publib.boulder. ibm.com/pubs/pdfs/redbooks/sg245183.pdf. This book is also available online at the IBM Redbook Web site at www.redbooks.ibm.com
• IBM Client Access OLE DB Support page: www.as400.ibm.com/clientaccess/oledb
• “Slam-dunking AS/400 Data into Microsoft Excel with OLE DB,” Brant Klepel, MC, May 1999

SDK Differences Between the Windows 95/NT Client and Express Client

The AS/400 SDK for ActiveX and OLE DB is compatible with Client Access for Windows 95/NT (the Windows 95/NT client) V3R1M3 and above. In Client Access Express for Windows (the Express client), the SDK is bundled into the Client Access toolkit and is loaded onto your computer when you load that option. For the Windows 95/NT client, however, you must load the software separately from its own directory on the Client Access for Windows 95/NT CD-ROM (V3R2M0 only), or you can download and install it from the Client Access OLE DB Support Web site (www.as400.ibm.com/ clientaccess/oledb). If you are installing the SDK from an earlier version of the Windows 95/NT V3R2M0 CD-ROM, there may be some incorrect instructions for loading the SDK printed on the CD-ROM’s label. To get the correct instructions, read IBM’s APAR II11448, “Incorrect Directions for SDK for ActiveX and OLE DB Install,” on the Client Access Informational APARs Web site at www.as400.ibm.com/clientaccess/caiixd1.htm
.

Also, be sure to always install the most recent Client Access or Express client service pack along with the SDK service pack, which is also available on the Client Access OLE DB Support Web site.

It’s worth mentioning that IBM support levels are different when running the SDK on a Windows 95/NT client PC than they are when running the Client Access Express toolkit on an Express client PC. For Windows 95/NT clients, the SDK is provided as a set of “as is” tools and samples for helping the application developer, and there is no formal support through IBM’s support line. The only way to report problems, ask questions, and provide feedback on the SDK is by sending an email to the development team at This email address is being protected from spambots. You need JavaScript enabled to view it.. The email account is usually monitored each workday, and IBM is fairly good at replying to questions. In contrast, the Client Access Express toolkit is formally supported by IBM, and IBM will accept support calls for the Express client version of the product.

The final difference between the Windows 95/NT client SDK and the Client Access Express toolkit version is the version of Visual Basic each product supports. The SDK version will run only with Microsoft Visual Basic

4.0 and 5.0. It is not compatible with VB 6.0. The Client Access Express toolkit version can be used only with Visual Basic 5.0 and 6.0. It is not compatible with VB 4.0. So as you start to develop VB code for AS/400 access, you will need to consider which version fits into your development and use the appropriate version. For more information on Microsoft Visual Basic incompatibilities with various SDK and Client Access Express toolkit versions, see “Why IBM’s AS/400 SDK for ActiveX and OLE DB Ran Afoul of Visual Basic 6.0 (and How to Correct It),” Joe Hertvik, AS/400 Network Expert (Web Edition), January/February 1999, www.midrange computing.com/ane/monthdisplay.cfm?md=19991.

IBM has alsopublished two APARs (which are also available on the Client Access APAR site) that explain what the problems are between the SDK and Visual Basic 6.0 and ActiveX Data Objects (ADO):

• APAR II11872, “Support for Microsoft Products and ADO Versions”
• APAR II11869, “SDK for ActiveX and OLE DB—Compatibility with Visual Basic 6.0”

-- Joe Hertvik

Figure 1: After installing the SDK, the AS/400 SDK menu item is automatically added to your VB Integrated Development Environment.

Figure 2: The Link to Tables window allows you to specify the AS/400 tables you want to open in your VB application.

Figure 3: You can automatically create code to display AS/400 data in five different types of forms inside a VB application.

Figure 4: You can display how your AS/400 data file fields will be displayed; as text or as data.