04
Mon, Nov
5 New Articles

Links, Imports, Exports: Using ODBC to Share OS/400 Data with Microsoft Access

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

Microsoft Access is a splendid tool for rapidly developing GUI forms and reports for users. It can be used to generate mailing labels, create charts, and allow users to inquire on the latest AS/400 data.

But, how do you get your AS/400 data into Access so that you can do all these things? You might be surprised to know how easy it is. In this article, I will demonstrate a few ways to exchange data between Access and the AS/400 using an ODBC data source.

Just a note before getting started: these examples were developed and tested using MS Access 97. Most of the examples will work with Access 2.0, 95, and 2000, but the menu options may be in a slightly different spot than stated here. Also, the code examples may need modification. Access 2.0 users will need to have the 16-bit version of Client Access installed and you will have to modify all of the code because Access 2.0 is not VBA (Visual Basic for Applications)-compliant.

Moreover, all of the examples here make use of the default *SQL naming convention (library.object) where the library name and object are separated by a dot (.) rather than by a slash (/). If you configure your ODBC data source to use the *SYS naming convention (library/object), you must remember to change all of the examples accordingly.

 

Linking Tables

 

I’ll begin by discussing linked tables. A linked table is a pointer to a table or view in an external database. A linked table could exist in a Fox Pro, SQL Server, or AS/400 database. The benefit of a linked table is that remote data can be accessed and, in some cases, modified right from the Access application without a shred of programming effort. In other words, even though data resides in tables on a remote server, the AS/400 data act like native Access tables.

For a hands-on approach, create a blank Access database. Then, follow the steps outlined here to create a linked table in your Access database using ODBC:

1. Configure a Client Access ODBC machine data source and call it AS400. For this example, be sure to include library QIWS in your library list. All other ODBC defaults can remain the same. (For help on configuring an ODBC connection, see the articles listed in the Related Materials section at the end of this article.)

2. Choose File/Get External Data/Link Tables. The LINK window will appear.


3. In the Files of Type box, select ODBC Databases.
4. A box with all of your PC’s ODBC data sources will appear. Click the Machine Data Source tab and select the AS400 data source.

5. A list box will appear showing all of the data files, including all tables (physical files) and views (logical files) from all libraries in your ODBC DSN library list. For this example, choose QIWS.QCUSTCDT. QIWS is a Client Access product library. QCUSTCDT is a sample customer master file that resides on most AS/400s. If you didn’t specify QIWS in your library list, this file will not appear. If, for some reason, you don’t have QCUSTCDT, then specify QSYS.SYSTABLES or some other table on your system. (Remember to change your ODBC library list to include QSYS if you do so.)

The one bad thing about Access in this scenario is that it will only show files for libraries in your ODBC library list. Therefore, it is imperative that the proper library list be specified in your ODBC configuration before attempting to link a table. Also, don’t specify unnecessary libraries as it will take longer for Access to build the list of available files.

One final tip on libraries: Be cautious about establishing the library list in your ODBC data source when developing a client/server application. ODBC opens up a whole can of security worms because a snoopy user can utilize a tool such as Access to browse and perhaps update any of the tables and views specified in the library list. Whereas green- screen applications may easily limit users to data via menu items, etc., no such security scheme exists in the ODBC realm.

6. If Access cannot detect a unique key on the table you are linking, it will prompt you with a SELECT UNIQUE RECORD IDENTIFIER window. Access is looking for you to identify a unique key on the file. This step is very important if you want to update your linked table data directly from Access. A linked table must have a unique key specified in order for updates and deletes to work. If Access doesn’t have a unique record identifier to work with, it will not allow updates. In my example, the demo customer table QCUSTCDT doesn’t have a unique key built, so it doesn’t matter what field(s) you select for the unique index identifier. Therefore, click CANCEL. (As a trial, you can use SQL or DDS to create a unique index over the file on the CUSNUM field. Then, when you link the table, Access will allow the data to be updated! Again, keep in mind the security factors when allowing users to access linked tables because they may be able to directly update your data DFU style.)

7. Finally, you are returned to the Access database window. Your QCUSTCDT table should appear in the list of tables. Notice that Access prefixed the table name with the library name QIWS.

8. Double-click on the QCUSTCDT table to view the contents of the table.
9. This table can now be used in Access queries, forms, and reports as though it was a native Access table. When the data in the table is updated on the AS/400, it will also be reflected in this linked table.

To delete a linked table, single-click on the table name and then press the delete key. Deleting the linked table does not remove the data from your AS/400; it just removes the link in the Access database. Also remember to close your view of the QCUSTCDT table before you delete the link because Access will not allow an open link to be removed.

The major benefit of linked tables is that the data are always available in real time from the AS/400. The major drawback of linked tables is that the performance decreases as the table size increases. Also, if the AS/400 is down, then the data is unavailable for viewing in Access.

 

Importing Tables

 

A second method of exchanging data is known as importing tables. Imported tables are “copies” of the live tables on the AS/400. At the time the import is done, all of the data in the selected table is copied into a native Access table. Like linked tables, imported tables are accessed via an ODBC data source.


To create an imported table, follow the initial steps for creating a linked table (except choose File/Get External Data/Import). As before, the steps to open your ODBC DSN and select an AS/400 table are the same. When selecting an import table, however, Access will not bother you with primary key concerns. Since the table will now be a native Access table, it will not need to know about primary keys.

For practice, attempt to import the data in file QIWS.QCUSTCDT into an Access table. (Once the table is imported, you may rename it by right-clicking on the table and choosing RENAME.)

One important thing to remember about Access is that it always processes its tables quicker when the table has a primary key. If your imported table has an identifiable primary key, create a primary key on the table. For table QCUSTCDT, this can be done by right- clicking on the table and choosing Design or Design View, then right-clicking on the CUSNUM field and choosing Primary Key. For those who want the process automated, execute a CREATE INDEX SQL statement using an action query, VBA code, or macro. For instance, the SQL statement used to create a primary key on the imported table QIWS_QCUSTCDT is Create Index CUSNUM on QIWS_QCUSTCDT (CUSNUM) with primary. Since QCUSTCDT only has a few records, there may not be a noticeable processing time difference. However, when working with a table of a few thousand records or more, a primary key can make a big difference in processing time.

The benefits of imported tables are that they are native Access tables, so Access can process them quickly. Also, the data is available even when the AS/400 is down or inaccessible. The major drawback of the imported table is that the data may not be current and that a maintenance program or user has to constantly refresh the data in the database by reimporting the table at regular intervals. For instance, some companies use Access to aid in month-end processing. Typically, they have to import a snapshot of all their important data tables just prior to month end. Doing so manually from month to month can become a tedious process.

 

Watch Out!

 

When sharing data between different database systems, there are many things to watch for. Here are some common Access flukes when importing/linking data from the AS/400:

• Numeric fields with a large field size are imported as type TEXT with 255 characters. However, once the data is imported, the field’s data type can be changed to type DOUBLE. Access will then convert the data from text to numeric. When using linked tables, there is nothing you can do about this fact. To convert the text data in a linked table back to numeric in a query or code, use the VBA VAL() function.

• Fields with a TIMESTAMP data type on the AS/400 contain a six-digit microsecond precision in the time portion of the timestamp. This microsecond precision data portion will be truncated when the field is converted to the Access DATE/TIME data type.

• When manually importing and linking, Access will prefix the table name with the library name.

• Every row in the AS/400 table will be imported or linked. If only a subset of the table data is desired, create an SQL view or select/omit logical file to limit the data and then import the subset view.

• Unfortunately, field text information does not get added to the Access table description.

 

Automate the Process

 

Table imports and links can be automated through the use of VBA code or macros. The VBA code to import a table or view from the AS/400 is given in Figure 1.

If you are linking a table rather than importing, make sure to change the transfer type constant from acImport to acLink on the TransferDatabase command.


The GetTable function can be called from things like macros or from a command button’s ON CLICK event with the following syntax: =GetTable(“QCUSTCDT”,”QIWS”). Note that the equal sign (=) preceding the function name is not always required, so pay attention for those cases where Access doesn’t want the equal sign (=).

GetTable first deletes the existing table in the Access database so that it can be reimported or relinked. Second, the data is either linked or imported from the AS/400. When importing, you may want to add your own logic to build indexes over the imported table.

Lastly, remember that after doing many table deletions and imports, the Access database size may become bloated because Access doesn’t reclaim the space from the deleted table until a database compact is run. The compact option can be run from Tools/Database Utilities/Compact Database. Using this option is necessary for keeping your database thin and trim.

 

Exporting Tables

 

Access offers an export function that conveniently allows Access data to be placed on the AS/400 via ODBC.

Take the following steps to export an Access table to the AS/400 using ODBC:
1. Select a native Access table in the database window by clicking once on the table
2. Choose File/Save As/Export.
3. When the Save As... dialog appears, choose To an External File or Database and click OK. In the Save Table As dialog, select ODBC Databases from the Save As Type list. The EXPORT box will prompt you to select a name for your exported table. (By default, this is the same as the Access table name. Choose a name that is compatible with AS/400 naming conventions. Be sure to make the name upper case without any special characters. The name should not be more than 10 characters in length.) By default, Access will export the table to the first library in your ODBC configuration library list. There is no easy way to get around this fact without resorting to some ugly code.

4. When you press Enter, you will be prompted to select your ODBC data source. (If necessary, you will also be prompted to enter your user name and password. If you are working on a slow connection, I suggest that you establish a connection to your AS/400 first using operations navigator or AS/400 connections; otherwise, you will connect and disconnect every time you do an export.)

If you don’t want to export an entire table, you can also write an Access query to filter and arrange the data and then export the results of the query.

 

More Flukes

 

Some common Access problems when exporting data to the AS/400 are as follows:

• When a field is blank or empty in MS Access, it contains a NULL value. When writing a program to process the exported Access data, you may (depending on the language) have to take special care to read files containing a null. For example, RPG/400 and older RPG ILE releases do not support nulls. However, there is a special compiler option on the Create RPG Program (CRTRPGPGM) and Create RPG Module (CRTRPGMOD) command to allow RPG programs to convert null values in externally described files to blanks so that RPG can make use of the file. To use this option, set the Allow null values parameter (ALWNULL) on the CRTRPGxxx command to Yes by adding the following parameter to your CRTRPGxxx statement:

ALWNULL(*YES)


name.

• As of V4R2, RPG ILE has a special %NULL function that will allow a field to be tested for nulls. If you do not want to worry about null-capable fields in your AS/400 table, however, make sure every field in your Access table has the REQUIRED property set to YES.

• When TEXT data types are exported, they are automatically converted to the VARCHAR data type on the AS/400. The same caution as above applies here. RPG/400 and pre-V4R2 RPG ILE compilers do not directly support the VARCHAR data type. Use the CRTRPGxxx Type conversions option compiler option CVTOPT(*VARCHAR) to convert the VARCHAR variables to fixed length character fields. Be careful to take all of these factors into account, because I was unable to successfully get an RPG/400 program to update a file that contained NULL capable and VARCHAR fields.

• Access data types of currency, single, and double are converted to the AS/400’s FLOAT data type. The FLOAT story is the same as with NULLs and VARCHAR except that FLOATs are not supported at all in RPG until V4R2 ILE.

• Access allows field names in its tables that are not supported by the AS/400. For example, Access allows field names with embedded blanks and certain special characters. The AS/400 will keep the long field name if it can (accessible with languages like SQL), but for the system field name (which is 10 characters or less and used by DDS and languages like RPG), the AS/400 will invent a field name for you. If you are planning on using a language like RPG to process your exported Access data, it is a good idea to name all of your fields in Access according to that language’s naming conventions. Make sure your field names are upper case, otherwise the long field names will be enclosed by double quotes. For example, the Access field Customer_number becomes “Customer_number” on the AS/400.

• DATE/TIME fields will be exported to the AS/400’s timestamp data type, which is disappointing because Access stores a date/time field in eight bytes, but the AS/400’s timestamp is 26 bytes. Again, check your language to make sure it can accept date/time fields. ILE RPG can do this, but the RPG/400 compiler requires yet another special Type conversion compiler option: CVTOPT(*DATETIME).

• The record format name and the table name will be the same for an exported table, so again, for languages like RPG, the record format will have to be renamed inside the program.

• DB2/400 doesn’t currently support the autonumber data type. This data type will be exported as a long integer.

• There are several things to keep in mind when sending a table from Access to the AS/400. As with field names, make sure that your export table name is upper case and doesn’t have any illegal characters, otherwise the object name will be enclosed by double quotes (“ “). (Objects surrounded by quotes can also be a pain to delete!) Also make sure the export name is 10 characters or less; otherwise, the system will assign a name for you. For example, if you export a table called TBLACCESSDATA, then the AS/400 will give it a name called TBLA_00001.

• When exporting the same table over and over, you will have to first delete the table on the AS/400. There is no replace option. You may use an Access passthrough query to issue a DROP TABLE statement to delete the table on the AS/400 before exporting.

• If speed is important, then, for large tables, you may want to resort to coding the export using ADO, ODBC, one of the Client Access ActiveX controls, or just the Client Access file transfer utility. Examination of the ODBC SQL trace for an Access export (Control Panel/ODBC 32-bit/Trace/Start Tracing Now) reveals that Access is executing a parameter INSERT statement (e.g., INSERT INTO table VALUES(?,?,...)) for every row in the table. 20,000 rows will result in 20,000 insert statements!

• Indexes on the table are not exported. If necessary, they will have to be created on the AS/400 after the export has completed. Again, a pass-through query can be utilized here to run the CREATE INDEX statement on the newly created AS/400 table.


 

Automate the Export

 

Table exports can also be automated through the use of VBA code or macros. The VBA code to export a table to the AS/400 is given in Figure 2. In general, I prefer code over macros because code allows more flexibility for error handling.

The PutTable function expects to be passed the name of an Access table (or query) and the name of the resulting AS/400 table. Remember, Access will place the table in the first library specified in the ODBC data source. Notice that when the export occurs (TransferDatabase method), the AS/400 table name is automatically converted to upper case and truncated at the 10-character limit.

For the next exercise, export the imported version of QCUSTCDT to a library other than QIWS on your AS/400. (This task can be done by changing your ODBC DSN default library list to make sure that the target library is the first library in the library list.) Then run the Display File Field Description (DSPFFD) command over both versions of the file and examine the results. You can print out the file description for each file by executing the DSPFFD command as follows:

DSPFFD LIB/QCUSTCDT OUTPUT(*PRINT)

LIB is the library containing the QCUSTCDT file you want to examine. You’ll see that the original file is composed of character and zoned fields. Generally, these data types are nonstandard data types for an SQL database. The export version of the file is composed of varchar, integer (binary), and floating-point fields. These data types are standard for an SQL database.

 

Total Access

 

Now that you know how easy it is to move entire tables between Access and the AS/400, give it a try. With the ability to link, import, and export data, Access may be used to smoothly control data flow between the PC and the AS/400.

I’ve only covered the basics of exchanging data with an entire table at once in this article. If this doesn’t quite fit your needs, Access has the ability to do a lot more!

 

Related Materials

 

“Configuring 32-Bit Client Access/400 ODBC, Part 1,” Shannon O’Donnell, Client Access/400 Expert, September/October 1998 “Configuring 32-Bit Client Access/400 ODBC, Part 2,” Shannon O’Donnell, Client Access/400 Expert, November/ December 1998 “Turbocharging ODBC for Client/Server Performance,” Howard F. Arner, Jr., Midrange Computing, December 1998

‘ Use these constants for ODBC
‘ for GetTable and PutTable

Global Const gcODBC = “ODBC Database”
Global Const gcODBCDSN = _

“ODBC;DSN=AS400;UID=USER;PWD=PWD;”

Function GetTable(sTable As String, sLibrary As String)

On Error Resume Next

‘ If user didn’t supply table/library, then prompt

If sLibrary = “” Then

sLibrary = InputBox(“Enter Library Name”, , “QGPL”)

If sLibrary = “” Then Exit Function
End If
If sTable = “” Then


sTable = InputBox(“Enter Table Name”)

If sTable = “” Then Exit Function
End If

‘ Delete Table if already exists

DoCmd.DeleteObject acTable, sTable
err = 0 ‘Reset Error Object

‘ Link/Import Table from ODBC Data Source
‘ Replace constant acImport with acLink for linked tables

DoCmd.TransferDatabase acImport, _

gcODBC, gcODBCDSN, acTable, _

sLibrary & “.” & sTable, sTable

If err Then

MsgBox err & “ “ & Error, , “Error Occured”

GetTable = True ‘Error
End If

End Function

Figure 1: The GetTable function is designed to automate the task of importing or linking tables.

Function PutTable(sAccTable As String, s400Table As String)

On Error Resume Next

‘ If user didn’t supply table, then prompt

If sAccTable = “” Then

sAccTable = InputBox(“Enter Table Name”)

If sAccTable = “” Then Exit Function
End If
If s400Table = “” Then

s400Table = sAccTable
End If

‘ Export Table via ODBC Data Source

DoCmd.TransferDatabase acExport, _

gcODBC, gcODBCDSN, acTable, _

sAccTable, UCase(Left(s400Table, 10))

If err Then

MsgBox err & “ “ & Error, , “Error Occured”

PutTable = True ‘Error
End If

End Function

Figure 2: PutTable is used to send an Access table or the results of an Access query to the AS/400.


Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


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: