If you’re an RPG programmer and programming front-end applications in Microsoft’s Visual Basic for Applications (VBA) seems like a foreign language, this is the article for you. Once you see how similar the two really are, you’ll be speaking fluent VBA in no time. For example, we will go through the creation of a simple order entry system using Microsoft Access for front-ends. That is, all of the interactive programming will be done in Microsoft Access. Our database will be stored on the AS/400 and accessed via ActiveX Data Objects (ADO).

System Requirements

To complete the examples in this article, we will use Microsoft Access 97, AS/400 Client Access Express for Windows on V4R4, and Microsoft ADO Version 2.1. To follow along with the examples, go to the Midrange Computing Web site (www.midrangecomputing. com/mc) and download the Microsoft Access database for this article.

The Database

This database contains all the forms and code that go along with this article. Also included in this database is a form that creates your AS/400 data files using ADO. The first time you open this database, you should proceed to the Forms tab and double-click the frmInstall form. You will be asked to enter your user name, password, and system name. When you click on the Install button, the database creates the AS/400 Library and files required for the example. This is accomplished by using SQL Create Database for the library and Create Table statements for the four files used in the example. This will also insert some sample records into the Customer and Item tables. The information entered on the frmInstall form is cached for later use when reading or writing to these tables via ADO.

It is important to note that you may have to change the references for this Access project. Microsoft has shipped many versions of ADO, and you may be running an earlier or later version than I was when I created this program. If you have a problem, open the Tools/Macro/ Visual Basic (VB) Editor menu choice and then select Tool/References from the VB Editor menus. If you see the word MISSING preceding any project reference entry, you need to correct that by deselecting the improper version of the component and selecting

the proper version of the referenced file. If you need a more up-to-date version of ADO, download the latest version of MDAC (Microsoft Data Access Components), which includes ADO and ODBC, from the Microsoft Web site (www.microsoft.com/data/).

Before we go any further, let’s go over the parts of a Microsoft Access database and the comparable AS/400 objects.

Tables. Tables are going to be fairly obvious. They would be the Microsoft Access version of an AS/400 physical file. Access tables also contain a component of a logical file in that they can have multiple indexes defined on one table. This is accomplished by displaying the table in Design view and going to the Indexes option under the View menu. Access indexes, like logical file keys, can have multiple key fields defined. These indexes can also be named through this screen. A table’s primary key (that is uniquely keyed) is named PrimaryKey by default.

Queries. Although this one would also seem obvious, it’s not. Queries in Microsoft Access would be more accurately compared with logical files or Open Query File statements than they would with AS/400 queries. This is because a primary use of queries in Microsoft Access is selecting, sorting, and grouping data to be used for another task at a later point. Queries within Microsoft Access are one of the most powerful tools within this development platform because of their ability to define a query that updates, appends, or deletes records from a table. Unfortunately, for the purposes of this article, we will not spend much time on Microsoft Access queries.

Forms. Microsoft Access forms are the equivalent of AS/400 display files on steroids. This is because you can build a data entry application that uses multiple tables in a form without ever typing a single line of code. For our application, we will create a form that is controlled by a code module.

Reports. Reports within Access are something between an AS/400 query and an RPG program. Although you don’t need to do any coding to create a report, adding a module to the report’s Print event allows you to customize certain report functions, like forcing the printing of group headings on page breaks.

Macros. Microsoft Access macros are like a combination of a CL program and an AS/400 command. Macros allow you to set a sequence of events to occur based on menu commands or modules.

Modules. Modules are the Microsoft Access equivalent of programs on the AS/400. The command language used is VBA, which is basically a downsized version of Microsoft’s VB development platform.

Now that we’ve gone over the basic similarities between developing in Microsoft Access and developing on the AS/400, let’s make some practical use of what we’ve gone over and see how programming in VBA compares to programming in RPG.

Before we start with the code, let’s examine the form that will act as our data entry screen. First, open the FRONTAL.MDB Microsoft Access database and go to the Forms tab. Now highlight the frmOrderEntry form and click the Design button to view the form shown in Figure 1. Believe it or not, each of the objects on this form has an AS/400 counterpart. For example, the combo boxes used for the Order Number, Customer, and Item fields act like an entry field with a prompt option on the AS/400. The nice thing about combo boxes is that you can link them to a field on a table without doing any coding simply by setting their RecordSource property. (To display the properties of a control, right-click on the control and select Properties from the pop-up menu.) An example is the OrderNumber field, which has the SQL statement “SELECT OrderNumber FROM tblOrderHeader” as its RecordSource. This tells Access to list the order numbers out of the tblOrderHeader field in this box. In the case of the Customer Number combo box, we want to be able to display the customer name in the field but return the customer number as the field value. This is accomplished by setting both the ColumnCount and the BoundColumn properties to 2. The

The Form

2 in the ColumnCount property tells Access to show two columns from the Select statement that drives this combo box, while the 2 in the BoundColumn property tells Access to place the contents of column number 2 into the current record when an entry is selected from the combo box.

Next, let’s examine the command buttons on our form. Command buttons are used to control the execution of a subprocedure. The control program flows in the same way an RPG program would use a function key. If you examine the Caption property, you will notice it shows as &New. The “&” tells Access to make this the shortcut key for this command button. When the form is displayed in the Form view, the button is displayed with the letter N underlined. The user can press Alt+N instead of clicking on the button. Since the button is named cmdNewOrder, both clicking the button and pressing Alt+N causes the cmdNewOrder_Click() subroutine to be activated. This is how routines are called in an event-driven environment like Access.

Next, let’s look at the list box on our form. List boxes differ from combo list boxes in that a combo box is initially displayed as a one-line field in which the list is dropped down by clicking on the combo box. A list box is always displayed in list form with a slide bar displayed along the side. The list box itself is used like the subfile display is used on the AS/400. Like the combo box, the list box can be linked directly to the table and fields required by the application. The RowSource property on this list box is slightly more complicated than those in any of the combo boxes. The following SQL statement not only selects which fields are displayed but also includes a calculated field for the extended order amount and a WHERE clause to select order lines for only the current order:

SELECT OrderLine, OrderItem, Quantity, Price, DueDate, [Quantity]*[Price] AS Amount
FROM tblOrderDetail WHERE (((OrderNumber)=[lstOrderNumbers]));

Also, the ColumnHeads property is set to “Yes” so that the list box shows the field names as the first row in the list. This list box automatically shows the order lines as they are added to the order...no coding required!

The text boxes on the bottom of the form act as standard entry fields would in a display file. On the tbxOrderLine text box, the enabled property is set to “N,” which is the equivalent of creating a protected input capable field on a display file. Entries made in these text boxes are added to the tblOrderDetail table and will then automatically appear in the list box above. These are all of the objects required by our example.

Next, let’s take a look at the code used behind the scenes for our application. To do this, click on the Modules tab and highlight the Global module and select Design. This module is named Global to tell Access to make the procedures in this module available to other modules, forms, or reports within this database.

The Imports function has four subroutines (Items, Customers, Header, and Details) that basically perform the same function. They all download copies of their related data files from the AS/400 to our Access database. Figure 2 is a modified snippet of the Imports function. The Dim statements are used to define the objects that will be used in the procedure. The first three Dim statements are used to set up our ADO objects. The ADO Connection object lets us define the information required to connect to the AS/400. This includes the system name, user name, and password. This is done through the ConnectionString property. When defining the ConnectionString property, you must set the Provider parameter to IBMDA400 to connect to an AS/400 database if you are using the Client Access OLE DB provider. You can set the ConnectionString argument to connect your AS/400 to an ODBC driver, also.

The ADO Command object is used to define what data file on the AS/400 will be used and which records from that file should be read. This is done through the use of

Modules

standard SQL code that is placed in the CommandText property of the ADO Command object. In our example, these are basic Select from File Name statements; however, these can be more detailed to select specific fields from specific records, and the selection can be built dynamically as needed. Using this method, your programs could access one record at a time for doing interactive updates. For our example, we have not done this, because we wanted the ability to use multiple list boxes containing all of the records in the data files. The ADO Recordset object is used to access the records and fields in the file defined by the Connection and Command objects. The final Dim statement defines another Recordset object. This one allows us to access information in one of our Access tables.

When this function runs, it first clears the data in the Access table by using the RunSQL method of the DOCMD statement to execute an SQL Delete from Table Name statement. Now, after defining the ADO Connection, Command, and Recordset objects, we will read through each record of our AS/400 table and create new records in the related Access table. The WHILE NOT tablename.EOF line allows us to perform a loop until the specified table is at end-of-file. This would be comparable to doing a DOWxx loop based on a no-read indicator on a Read statement. The MoveNext method, when used on the ADO Recordset, works like an RPG Read statement. The AddNew method tells Access to create a new empty record in our local table. The Fields property allows us to set (or read) the value of the field name specified. The Update method replaces the blank record with actual field values. These three steps perform the same function as a Write statement in RPG. The four subprocedures used to import the AS/400 data are all called from the FormOpen event on our order entry form.

The cmdNewOrder_Click() subprocedure, a snippet of which is shown in Figure 3, uses the same ADO logic to take the data from the Order Header and Details tables and populate it back into the AS/400 Order Header and Details files. This routine runs each time an order is added or updated from our form and runs only on the current record (unlike the import routines, which dealt with all of the data in the file). If the cmdNewOrder button caption does not read “New,” the program updates the data on the AS/400 by using the Edit method on the ADO Recordset object after using the FindFirst method on that object to locate the correct record. After setting the fields values using the Fields property, we again use the Update method to write the updated field information to the file. If the caption of the cmdNewOrder button reads “New,” the program executes an SQL Insert statement to cause the data to be written to the AS/400 and to create a new order header record in the Access database.

That’s all that is required to get this application up and running. Unfortunately, there is a lot more code in there that does a lot of useful things. I’m sure that once you’ve seen how easy it is to complete the basic version of this order entry application, you’ll want to add your own customizations. This basic example gives you all the tools you need to gain full frontal access to your AS/400.

References and Related Materials:

Microsoft Universal Data Access Web site: www.microsoft.com/data/

Figure 1: The Design view in Access allows you to edit the properties and controls used in your GUI application.

Function Imports()

' These lines are comparable to 'F' spec
Dim conn As New ADODB.Connection

'Define ADO Connection
Dim cmd As New ADODB.Command

'Define Command Object
Dim tblCustomer As New ADODB.Recordset

'Define ADO recordset for Customers File
Dim tblCustomers As Recordset

'Define Accessrecordset for Customers File

'First we define our connection Provider will always be IBMDA400.
'Other options should be changed to match your system,
'user name & password

On Error GoTo OpenErr

conn.ConnectionString = "Provider=IBMDA400;" & _

"User ID=username;Password=password;Data Source=sysname"

conn.Open 'Open the connection

'Disable displaying of warning messages
DoCmd.SetWarnings False

'Remove customers from Access table
DoCmd.RunSQL "DELETE * FROM tblCustomers"

cmd.ActiveConnection = conn

'Customer Import
'This would be comparable to an OPNQRYF statement
cmd.CommandText = "SELECT * FROM OELIB.CUSTOMERS"

'Set recordset using SQL statement
Set tblCustomer = cmd.Execute

Set tblCustomers = CurrentDb.OpenRecordset("tblCustomers")

'This code would be comparable to an RPG 'DO' loop
While Not tblCustomer.EOF With tblCustomers

.AddNew

.Fields("CustomerNO") = tblCustomer.Fields("CUSTNO")

.Fields("CustomerName") = tblCustomer.Fields("CUNAME")

.Fields("CustomerAddr1") = tblCustomer.Fields("CUADD1")

.Fields("CustomerAddr2") = tblCustomer.Fields("CUADD2")

.Fields("CustomerCity") = tblCustomer.Fields("CUCITY")

.Fields("CustomerState") = tblCustomer.Fields("CUSTAB")

.Fields("CustomerZIPCode") = tblCustomer.Fields("CUZIPC")

.Fields("CustomerPhone") = tblCustomer.Fields("CUPHON")

.Update

End With

'This is comparable to the RPG Op Code READ

tblCustomer.MoveNext
Wend

'This would be comparable to an RPG CLOSE operation
tblCustomers.Close

...... 'More code to import

End Function 'This is a snippet of the cmdNEwOrder_Click sub procedure

If cmdNEwORder.Caption = "&New" Then

'Add new header
Set tblOrderHdr = cmdOH.Execute

If tblOrderHdr.RecordCount > 0 Then

tblOrderHdr.MoveLast

'Read the last order number
'and increment by one

LastOrder = tblOrderHdr.Fields("ORDNO") + 1

Else

LastOrder = 1

End If

cmdOH.CommandText = "INSERT INTO OELIB.ORDHEADER" & _

" (ORDNO, ORCUSN, ORTOTL) VALUES(?, 0, 0)"

cmdOH.Parameters(0) = LastOrder

'Replaces the first ? above with this value

cmdOH.Execute

'Write out blank Header record
'to grab order number

tblOrderHeader.AddNew

tblOrderHeader.Fields("OrderNumber") = LastOrder

tblOrderHeader.Update

lstOrderNumbers = LastOrder

'Position list to current order number

lstOrderNumbers.Requery

lstCustomer = Null

lstItem = Null

tbxOrderLine = 1

tbxQty = Null

tbxPrice = Null

tbxDueDate = Null

lstOrderLines.Requery

cmdNEwORder.Caption = "&Update"

cmdAdd.Caption = "A&dd"
Else

'Update header info

cmdOH.CommandText = "UPDATE OELIB.ORDHEADER " & _

"SET ORCUSN = ? , ORTOTL = ? WHERE ORDNO = ? "

cmdOH.Parameters(0) = lstCustomer

cmdOH.Parameters(1) = 0

For X = 1 To lstOrderLines.ListCount - 1

cmdOH.Parameters(1) = cmdOH.Parameters(1) + lstOrderLines.Column(5, X)

Next X

cmdOH.Execute

tblOrderHeader.Seek "=", lstOrderNumbers

If Not tblOrderHeader.NoMatch Then

tblOrderHeader.Edit

tblOrderHeader.Fields("CustomerNumber") = lstCustomer

tblOrderHeader.Fields("OrderTotal") = cmdOH.Parameters(1)

tblOrderHeader.Update

End If

cmdNEwORder.Caption = "&New"

lstOrderNumbers = Null

lstCustomer = Null

lstOrderLines.Requery

End If
End Sub

Figure 2: This snippet of the Imports function relates the Access code to the code you would write in RPG.

Figure 3: This portion of the cmdNewOrder subprocedure either creates a new order header or updates the existing order record on your AS/400.

Mike Faust is a senior consultant/analyst for Retail Technologies Corporation in Orlando, Florida. Mike is also the author of the books Active Server Pages Primer, The iSeries and AS/400 Programmer's Guide to Cool Things, JavaScript for the Business Developer, and SQL Built-in Functions and Stored Procedures. You can contact Mike at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Mike Faust available now on the MC Press Bookstore.

		Active Server Pages Primer Learn how to make the most of ASP while creating a fully functional ASP "shopping cart" application. List Price $79.00 Now On Sale
		JavaScript for the Business Developer Learn how JavaScript can help you create dynamic business applications with Web browser interfaces. List Price $44.95 Now On Sale
		SQL Built-in Functions and Stored Procedures Unleash the full power of SQL with these highly useful tools. List Price $49.95 Now On Sale

Also Read

Post to a Web Form Using Visual Basic

An Easier Way to Find OS/400 Job Numbers in Multi-tiered Applications

Hang a Left at Calendarville and Head to the AS/400 Meeting Scheduler