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.

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?