Let’s face it. In a world dominated by graphical PC interfaces, green-screen applications are things of the past. To effectively present AS/400 data, one needs to capitalize on the graphical strengths of PCs. With a basic knowledge of a PC language and SQL, you can easily learn to harness the combined power of the PC and DB2/400.

In this article, I will demonstrate PC-to-AS/400 connectivity using Microsoft’s Visual Basic for Applications (VBA) as the PC development language. I will use IBM’s SQL/400 to retrieve data from the AS/400. I will also use Microsoft’s ActiveX Data Objects
2.0 (ADO 2.0) to enable the two environments to communicate. This article will primarily serve as an introduction to ADO and its syntax. For those who need help getting started, there are a myriad of books that teach VBA. IBM’s SQL/400 manuals can be accessed at publib.boulder.ibm.com/html/as400/infocenter.html. An AS/400-specific ADO reference, A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support (SG24-5183-00), is also available at www.redbooks.ibm.com. This manual is a great reference tool.

For this exercise, Excel 2000 will be used as the demonstration environment. With little modification, developers using Office 95 and 97, 32-bit versions of Visual Basic (VB), Active Server Pages (ASP) Web programmers, and just about any other language that can accept a library extension can also use these examples.

What Is ADO and Why Use It?

Microsoft has declared ADO the successor to the ODBC interface. Basically, ODBC allows PCs to access data from multiple database platforms via a standard SQL interface. In addition to SQL database access, ADO provides record-level access and access to non- database items such as email or text. In AS/400 terms, these non-database items include executing commands and accessing data queues.

Prior to ADO, an AS/400 developer would have to learn ODBC for database access and would also have to make use of several APIs or ActiveX controls to access commands and data queues. Now, ADO provides access to all of these features in a single interface. In other words, ADO provides a single toolbox for many AS/400 objects.

ADO is an ActiveX control and, like other ActiveX controls, it has a set of properties and methods that give PC developers access to data on a remote server. As with ODBC, each database engine needs to have a special driver. In ADO terminology, these drivers are called OLE DB providers. Hence, SQL Server, Oracle, and the AS/400 will

each have its own OLE DB provider. Each provider translates the generic ADO functions into platform-specific instructions. For platforms that don’t yet have an ADO provider, a generic ODBC provider is available that allows ADO to be used with any ODBC data source.

Making a Clean Start of It

Before you start programming an Excel macro for AS/400 OLE DB access, check your AS/400 to make sure PTF levels are current and that the host servers are running. (Perform this task by running the AS/400 ‘STRHOSTSVR *ALL’ command.) On the PC, you will need to download IBM’s OLE DB provider (ADO driver) at www.as400.ibm.com/ clientaccess/OLEDB/. Currently IBM’s ADO 2.0 OLE DB provider is still in beta and is only available with the AS/400 Client Access Express for Windows (Express client) beta. If you do not want to bother downloading IBM’s ADO driver until it is released, you may use the standard ODBC driver that comes with ADO 2.0. If you will only be using the ODBC provider, then you must download ADO 2.0 from Microsoft’s Web site at www.microsoft.com/data/.

Let’s Demo This Puppy

What if your vice president of sales wants to know the sales figures for the products sold during a given date range? (See Figure 1.) A graphical representation of the data would be helpful. The PC spreadsheet offers a solution because it allows the user to view, sort, group, and manipulate the data after it is downloaded. The spreadsheet also places the data in an interchangeable format for sharing and can generate a graph. This demonstration shows the simplicity and power of programmatically retrieving AS/400 data into Excel.

For this example, I placed some data from Microsoft’s Northwind database on the AS/400. (Northwind is a sample database that comes with Microsoft Access and VB.) The sample data consists of three tables: Orders, Order Details, and Products. The Order Details table was renamed to OrderDtl to conform to AS/400 naming conventions. With the exceptions of the OrderDtl name change and the parameter definition of the date data type, this entire demonstration will work with the Northwind database using the Microsoft Access OLE DB driver.

Setting Up Your Spreadsheet Architecture

The spreadsheet will consist of one workbook containing two worksheets. Worksheet one (sheet1) will contain a summary of the products sold over a requested date range. Worksheet two (sheet2) will contain the graphical representation of the data in sheet1.

Start Excel and open a new workbook. On sheet1 of the workbook, enter the desired headings. (See Figure 1.) My example uses three columns: Qty, Product, and Amount. My example also requires two special cells to be reserved for entering the order date range. To have your macros work correctly, the order date range fields must reside in spreadsheet cells B3 and B4. If you place them anywhere else, you will get an improper date type error from your AS/400 query. Finally, a command button is utilized to activate the data retrieval from the AS/400. (A command button can be inserted via the Excel Forms toolbar.

Creating a command button should actually be the last step in the process after the VBA macro has been created.)

The Programming Interface

After the headings are set up, enter the VB Editor (Tools/Macro/Visual Basic Editor). In the Editor, add the Microsoft ActiveX Data Objects 2.0 Library to the list of references (Tools/References). Referencing the ADO 2.0 library expands Excel’s capabilities to include all of ADO’s features. Next, insert a module (Insert/Module). All of the code from this example can be placed in the default module (module1). Note also that Excel considers all subroutines created in VBA to be user-defined macros.

Establishing a Connection

The SetADOConn function (Figure 2) is a generic function that can be used in any VBA application. Establishing a connection to the AS/400 is simple. Define an ADO connection variable (gADOConn) and assign it a provider and a data source name. Again, the provider is the driver that lets ADO know what kind of backend database it is talking with. IBM’s OLE DB provider is IBMDA400. The data source is the AS/400 system name. User name and password information may also be supplied in the .Open method. If you’re using this code to program your own OLE DB interface, be sure to change the USER and PASSWORD literals in the .Open method to a valid AS/400 user and password for your system. You must also change the data source name (AS400, in this case) to be your AS/400 system name or IP address.

Keep in mind that making the initial connection is a relatively slow process. It is wise to establish a single, global connection so that it can be used by all subroutines in the project. Establishing a new connection for every server request will give new meaning to the word slow.

If you don’t have IBM’s OLE DB provider, you can use the ODBC provider by substituting a complete ODBC DSN string in the .Open method:

.Open
“DSN=AS400;UID=USER;PWD=+
PASSWORD;XDYNAMIC=1;DBQ=+
DATALIB;”

Regardless of whether you use OLE DB or ODBC, the variable gADOConn is defined as a global ADO connection variable and will store all of the necessary connection information. All subsequent requests to the AS/400 will be routed through this connection definition.

Submitting SQL Statements

Once the connection is established, the AS/400 is ready to receive instructions from the PC. The Retrieve Product Information subroutine (RtvProdInfo) in Figure 3 demonstrates how to submit an SQL SELECT statement with parameters, retrieve and copy the data into the spreadsheet cells, and format a data column for display.

There are three ADO variable types that need to be defined. The first type is the ADO command. This type is primarily used for executing AS/400 commands for submitting SQL SELECT queries with parameters and for action SQL statements such as UPDATE or DELETE. In this example, the ADO command is defined through the adoCMDProd definition.

The adoCMDProd definition is static in scope. Once it has been set, it will retain its definition in subsequent calls to the subroutine. Its .ActiveConnection property references the global connection variable gADOConn, telling it how to communicate to the AS/400. The .CommandText property contains a command or, as in this case, an SQL statement.

One disappointing note worth mentioning is that IBM’s provider currently doesn’t have a way to specify a default library name. If libraries are not specified in the SQL statement, ADO will attempt to use the user name as the library name.

The question marks (?) in the SQL statement are known as substitution markers. They will be replaced with parameters at the time of execution (in this case, the from and to order dates for data to be displayed on the spreadsheet).

The second ADO data type is the parameter (defined as adoParm and set in this example on the Set adoParm statements). As seen in the Figure 3, ADO parameters are defined with a name, data type, direction and length. The direction property is available for defining parameters as input, output, or both. Once defined, the parameters are appended to the command’s parameters collection. This step creates the association between the substitution markers in the SQL statement (the question marks) and the command. The

parameter definitions must match the order of the substitution markers in the statement,
i.e., the first substitution marker must match the first parameter definition, etc. The third ADO data type is the recordset. A recordset can be thought of as a two- dimensional array that contains the field and record contents for a given SELECT statement. In my example, the resulting recordset will have three elements or fields—Qty, Product, and Amount. In SQL terms, a recordset is the equivalent of a cursor.

The recordset is opened by referencing the .Execute method of the command variable, telling ADO to submit the SQL statement to the AS/400 for processing. The data will be returned via the recordset object. All of the recordset’s fields can then be referenced by field number or by name. Additionally, the .EOF property is provided to detect the end of file, and the .MoveNext, .MovePrevious methods, etc. are available to access the records. (A recordset may also be opened by specifying a nonparameter SELECT statement.)

In Figure 3, a loop is established to read all of the records and place the field data into the worksheet’s cells. When the loop is finished, the recordset is closed, column C is formatted as currency, and a subroutine is called to create the chart.

As the final step in making the spreadsheet functional, go back to sheet1 and create a Retrieve Data button. To create a command button, display the forms toolbar (View/Toolbars/Forms) and click the button icon. Assign the macro RtvProdInfo() to the button when prompted.

Creating the Chart

See the code in Figure 4 for creating the data chart. While the code may seem long, don’t be intimidated because Excel created most of it. To have Excel create the code, I used the Record Macro feature (Tools/Macro/Record New Macro). When the macro recorder is on, almost everything the user does (e.g., menu options and data entry) is recorded. When the macro recorder is stopped, everything that was recorded is converted to a VBA macro. Go to the VB Editor to view the macro.

With the macro recorder on, I used the chart wizard to create a chart on sheet2 (Figure 5). I then resized the chart, toyed with a few of its properties, and clicked the stop record button. To my delight, Excel had created a new VBA macro that provided the necessary code to create the chart.

When I started this project, I had no idea how to create a chart using VBA, so I let Excel show me how. One of the great features of Excel is that it can be used to create VBA code automatically! In fact, just about anything that can be done via menus in Excel can also be done using a VBA macro. The moral of this story is don’t spend hours combing through manuals to figure how to code something in Excel. Let Microsoft do it for you.

Users with Excel 2000 and a Web server can take this demonstration a step further by dynamically publishing the results to a Web page. Or users of an email client such as Outlook 98 can automatically email the spreadsheet to a predefined distribution list. Get out of the rut of doing things manually!

Forging Ahead with OLE DB

I’ve only scratched the surface of ADO’s capabilities in this article. There are a lot of easy-to-program possibilities once you learn the techniques.

ADO is a great benefit for the AS/400 developer because it provides a single interface to a variety of AS/400 features including access to database files, commands, and data queues. With ADO, there is no more need to learn a separate ActiveX control or API for each of these functions. In my opinion, ADO also requires less maintenance than ODBC.

ADO is a useful tool for combining the graphical capability and versatility of PC applications with the power of AS/400 applications. It behooves all AS/400 client-server developers to take advantage of this technology.

References

A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support, Redbook (SG24-5183-00), available online at www.redbooks.ibm.com

IBM AS/400e Information Center: publib.boulder.ibm.com/html/as400/infocenter.html

Related Reading

“Slam-dunking AS/400 Data into Microsoft Excel with OLE DB,” Midrange Computing, May 1999

“Microsoft Office 2000: Another Fish in the Groupware Sea,” Midrange Computing, April 1999

“Making PC Application-to-AS/400 Programming a Snap with IBM’s AS/400 SDK for ActiveX and OLE DB,” AS/400 Network Expert, January/February 1999

“Why IBM’s AS/400 SDK for ActiveX and OLE DB Ran Afoul of Visual Basic
6.0 (and How to Correct It),” AS/400 Network Expert (Web Edition), January/February 1999, www. midrangecomputing.com/ane/99/01

“Programming in Visual Basic Using the IBM AS/400 SDK for ActiveX and OLE DB,” Midrange Computing, October 1998

“Configuring 32-Bit Client Access/400 ODBC, Part 1” Client Access/400 Expert, September/October 1998

“Configuring 32-Bit Client Access/400 ODBC, Part 2,” Client Access/400 Expert, November/December 1998

Figure 1: Here’s a graph of sales figure I created in Excel 2000 by using an ADO interface to my AS/400.

'

' Define global variables
'

Public gADOConn As ADODB.Connection

Function SetADOConn() As Boolean
'

' Function:
' Set global connection to ADO data source
'

' Returns:
' False - if successful
' True - if failed
'

On Error Resume Next

If gadoConn Is Nothing Then

Set gadoConn = New ADODB.Connection

'

' Specify name of AS/400 OLE DB provider

' and system name (Ex. S1024200)

'

gadoConn.Open "Provider=IBMDA400;_

Data Source=S1024200;"

, "USER","PASSWORD"

If Err Then

MsgBox "Could not connect to ADO data source"

Set gadoConn = Nothing

SetADOConn = True

End If
End If

End Function

Figure 2: Use this code to establish an ADO connection to an AS/400.

Sub RtvProdInfo()
' Retrieve Product Information
'

' This macro will retrieve summary info
' for all products for the given
' date range.

'

' It will then graph the resulting info
' on Sheet2.

'

Dim ws1 As Worksheet 'Worksheet 1 (Product Info)
Dim ws2 As Worksheet 'Worksheet 2 (Chart)
Dim adoRSprod As ADODB.Recordset 'ADO Recordset-Product Info
Dim adoParm As ADODB.Parameter 'Parameter
Static adoCMDprod As ADODB.Command 'ADO Command
Dim iRow As Integer

If SetADOConn() Then Exit Sub
'

' Set Reference to Worksheet 1
'

Set ws1 = Worksheets!Sheet1 'Data Sheet
ws1.Range("A7", "C100").Clear 'Clear Cells
iRow = 7 'Start row

If adoCMDprod Is Nothing Then _

Set adoCMDprod = New ADODB.Command

With adoCMDprod

If adoParm Is Nothing Then

.ActiveConnection = gadoConn

.CommandType = adCmdText

.CommandText = " SELECT Sum(B.Quantity) AS Total_qty, " & _

" C.ProductName, " & _

" Sum(B.UnitPrice*B.Quantity) AS Amount " & _

" FROM MDRNGCOMP.Orders A, " & _

" MDRNGCOMP.OrderDtl B, " & _

" MDRNGCOMP.Products C " & _

" WHERE A.OrderDate Between ? AND ? " & _

" AND A.OrderID=B.OrderID " & _

" AND B.ProductID=C.ProductID" & _

" GROUP BY C.ProductName"

' Define Date Parms

' NOTE: Date data types are defined as char on the AS/400

Set adoParm = .CreateParameter("FromDate", adChar, adParamInput, 10)

.Parameters.Append adoParm

Set adoParm = .CreateParameter("ToDate", adChar, adParamInput, 10)

.Parameters.Append adoParm

End If

'

' Set Date Parameters

' Must be in YYYY-MM-DD format for the AS/400

'

.Parameters(0) = Format(ws1.Cells(3, 2), "yyyy-mm-dd")

.Parameters(1) = Format(ws1.Cells(4, 2), "yyyy-mm-dd")

Set adoRSprod = .Execute

With adoRSprod

'

' Loop through all records & place data in

' worksheet 1.

'

Do Until .EOF

ws1.Cells(iRow, 1) = !Total_Qty

ws1.Cells(iRow, 2) = !ProductName

ws1.Cells(iRow, 3) = Format(!Amount, "########")

iRow = iRow + 1

.MoveNext

Loop

.Close

End With
End With
'

' Format Column for Currency
'

ws1.Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00"
'

' Create Chart on Sheet 2
'

Set ws2 = Worksheets!Sheet2

Dim c As ChartObject
'

' Delete Old Chart(s) first
'

For Each c In ws2.ChartObjects

c.Delete
Next

CreateChart ("B7:C" & Trim(Str(iRow - 1))) 'Create Chart

End Sub

Figure 3: Use this code to create the RtvProdinfo macro.

Sub CreateChart(sDataRange As String)
'

' CreateChart Macro
' Receive Parm sDataRange - Data range to graph
' ex. B7:C15
Sheets("Sheet2").Select
Charts.Add

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(sDataRange)
ActiveChart.Location WHERE:=xlLocationAsObject, Name:="Sheet2"

With ActiveSheet.Shapes(1)

.ScaleWidth 1.48, msoFalse, _

msoScaleFromBottomRight

.ScaleHeight 1.35, msoFalse, _

msoScaleFromBottomRight

.ScaleWidth 1.25, msoFalse, msoScaleFromTopLeft

.ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
End With

With ActiveChart

.Legend.Delete

' Format Chart Title

.HasTitle = True

With .ChartTitle

.Characters.Text = "Product Summary"

.Font.Size = 12

End With

' Format X Axis

With .Axes(xlCategory, xlPrimary)

.HasTitle = True

With .AxisTitle

.Characters.Text = "Product"

.Font.Size = 10

End With

End With

' Format Y Axis

With .Axes(xlValue, xlPrimary)

.HasTitle = True

With .AxisTitle

.Characters.Text = "Amount"

.Font.Size = 10

End With

End With
End With
'

' Set Font Size for Axes
'

With ActiveChart.Axes(xlCategory).TickLabels

.AutoScaleFont = True

.Font.Size = 8
End With
With ActiveChart.Axes(xlValue).TickLabels .AutoScaleFont = True

.Font.Size = 8
End With

End Sub

Figure 4: The Create Chart macro code can also be generated with Excel’s macro recorder.

Figure 5: The final product! I created this chart by using the code and techniques in this article.