Introduction.
IBM iSeries (AS400) DB2 tables can be converted into dBase format, downloaded, and then linked or imported into MS Access databases. The downloading process is typically initiated and run from the AS400 menus. If multiple steps are required before transferring the output file to the local drive, this process can be automated using macro scripts. Keystrokes can be recorded and modified so that the target file is saved to a specific location on the local machine with a predefined name, allowing it to remain linked to the Microsoft Access database.
You need the necessary Access privileges to the iSeries (AS400) mainframe tables, and you can link the table directly into your MS-Access database using an ODBC-based procedure. We will explore this aspect shortly.
Typically, Reports are generated on the AS400 and delivered to users as hard copies or exported as text-based spool files if a soft copy is required. These files can be downloaded in text file format or imported into Microsoft Excel—though Excel may not always parse numbers, dates, or other formats correctly. AS400 tables can also be downloaded directly into Excel. If the number of rows exceeds 65,535 (the limit for an Excel worksheet), multiple worksheets will be created automatically to accommodate all the data.
Linking to IBM iSeries DB2 Tables.
Let us review the steps for linking IBM iSeries DB2 Tables in MS-Access Database. The example images are created from the Windows 2000 Workstation.
Creating ODBC System DSN.
- Select Start Menu -> Settings -> Control Panel -> Administrative Tools -> double-click on Data Sources (ODBC).
The ODBC Data Source Administrator Settings will be displayed. See the Image given below. The Following Steps will walk you through the procedure:
Select the System DSN Tab on the ODBC Data Source Administrator.
Click Add ... Button to display a list of Data Source Drivers.
Select Client Access ODBC Driver (32-bit) from the displayed list in the Create New Data Source Control and click Finish.
Type a name in the Data Source name Control. I have inserted the name myData as the Data Source Name. We have to look for this name when we attempt to link the Table to MS-Access.
Click on the Server Tab.
Type the specific iSeries Folder Name where your data table resides in the Library List Control. If more than one Library File, separate them with Commas.
- Select the Read-Only (Select statements only) option under Connection Type to ensure that we have no intention to modify the data in the iSeries Table.
Click the Apply button followed by the OK Button. The System Data Source Name myData appears in the System DSN Tab. See the image below.
Click OK to close the ODBC Configuration Main Control (the Apply button remains disabled in this case).
Linking to MS-Access Database.
Open your MS-Access Database.
Select File -> Get External Data -> Link Table or Import Option.
Select ODBC Databases in the Files of Type control in the Common Dialog Control and click the Link (or Import) Button, as the case may be.
Select the Machine Data Source Tab on the ODBC Control and find the Data Source Name myData that you have created, select it, and click OK.
You will be prompted for AS400 User ID and password. Key in your User ID, Password, and click OK.
The Tables list will open up, showing all the Table Names available in the AS400 iSeries Machine, prefixed with the Library Name followed by a period.
Select the Table(s) to Link and Click OK.
The Fields of the selected Table will be displayed, suggesting highlighting one or more fields for indexing if needed.
Highlight the field(s) if you would like to create a Unique Index for the Table; otherwise, click OK without selecting any.
The selected Table will be linked (or imported as the case may be) into your Database.
The AS400 Login Issue
If the table remains linked, whenever you attempt to use the table after opening your MS-Access Database, it will prompt for the AS400 iSeries UserID and Password. The login is valid for the current Session of the Access Database only.
If you don't want this to happen in the middle of some process, it is better to invoke the login immediately after opening the Database. To do this, create a Query, Form, or Report that uses the linked iSeries Table, and opens it with an Autoexec Macro or the Form in Startup. Even better, create a VBA routine to open the linked table that invokes the login procedure, and the User Logon at the beginning of the current session. This will take care of the rest of the Session time.
this type of data transfer is not possible in windows xp. I am getting junk data when data are being transferred from as400 to ms access. But same thing is possible in windows 2000. I do not know , what is the reason? Pls advise me. Amitmohan
ReplyDeleteI could not test the procedure under WindowsXP. Now I am retired from service I don't have access to AS400 System to try it out either. But, I don't see why it cannot work under WindowsXP. Try to insert the ODBC String into the ConnectStr Property of a new Query and open the AS400 Table directly rather than linking it to the database. Sample ODBC String is given below.
ReplyDeleteODBC;DSN=myData;UID=UserID;PWD=Password;TABLE=PAVUP.APC161D
You may read the Article: http://www.msaccesstips.com/2008/09/source-connect-str-property-and-odbc/ to take the ODBC string from your DSN.
Regards,
[...] LEARN MS-ACCESS TIPS AND TRICKS » Blog Archive » Linking with IBM … [...]
ReplyDeletedriver Search...
ReplyDelete[...]worth a try - even though we provide external links we aren't responsible for th[...]...