29
Fri, Nov
0 New Articles

Full Frontal Access

Visual Basic
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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/


Full_Frontal_Access05-00.png 397x234

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

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 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 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 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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: