Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Linking with IBM AS400 Tables

Introduction.

We can convert IBM iSeries (AS400) DB2 Table into dBase Format, download and Link or Import into MS-Access Databases. The downloading procedure must be created and run from AS400 Menus. If it requires several steps before the output file being transferred to local drive then it can be automated with Macro Scripts. The key strokes can be recorded and modified in such a way that the target file goes to a specific location on the Local Machine with a predefined name that remains linked with the Microsoft Access Database.

If you have sufficient Access Privileges to iSeries (AS400) Main Frame Tables you can Link them directly into your MS-Access Database. We are going to look into this method with ODBC based procedure shortly.

Normally, Reports are generated from AS400 and provided to Users as Hard Copies, or converted into Report Spool File in Text Form, if Soft Copy is requested for. We can download this File either in Text form or upload it into Microsoft Excel (may not Parse the data correctly into Number or Date etc.) with iSeries Report Down-Loader Program. AS400 tables also can be downloaded directly into Microsoft Excel and in this process, if the number of lines are more than 65535 (the limitation of Microsoft Excel Worksheet) it will create more than one Worksheet automatically to accommodate all the data into Excel File.


Linking to IBM iSeries DB2 Tables.

Let us go through the steps for Linking IBM iSeries DB2 Tables into MS-Access Database. The example images are created from Windows2000 Workstation.

Creating ODBC System DSN

  1. 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:

  2. Select System DSN Tab on the ODBC Data Source Administrator.
  3. Click Add . . . Button to display a list of Data Source Drivers.
  4. Select Client Access ODBC Driver (32-bit) from the displayed list in the Create New Data Source Control and Click Finish.
  5. Type a name in the Data Source name Control. I have inserted the name myData as Data Source Name. We have to look for this name when we attempt to link the Table to MS-Access.
  6. Click on the Server Tab.
  7. Type the specific iSeries Folder Name where your data table resides in the Library List Control. If more than one Library is there then separate them with Comas.
  8. Put a check mark on the Read-Only (Select statements only) option under Connection Type, to ensure that we have no intention to modify the data in iSeries Table.
  9. Click Apply followed by OK Button. The System Data Source Name myData appears in the System DSN Tab. See the image below.
  10. Click OK to close the ODBC Configuration Main Control (the Apply button remains disabled in this case).

Linking to MS-Access Database.

  1. Open your MS-Access Database.
  2. Select File - -> Get External Data - - > Link Table or Import Option.
  3. Select ODBC Databases in the Files of Type control in the Common Dialog Control and Click Link (or Import) Button as the case may be.
  4. Select 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.

    A List will open up showing all the Table Names available in the AS400 iSeries Machine Prefixed with the Library Name following by a period.

  5. Select the Table(s) to Link and Click OK.

    The Fields of the selected Table will be displayed suggesting to highlight one or more fields for indexing, if needed.

  6. 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 for the first time it will prompt for the AS400 iSeries UserID and Password and after that the login is valid for the current Session.

If you don't want this to happen in the middle of some processing steps it is better to invoke the login immediately after opening the Database. To do this, create a Query or Form or Report using the linked iSeries Table that opens with an Autoexec Macro or the Form in Startup, or even better write a VBA routine to open the linked table which will invoke the login and the User is prompted for keying in her User ID and Password at the beginning of the current session itself. This will take care of the rest of the Session.

Share:

4 comments:

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

    ReplyDelete
  2. I 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.

    ODBC;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,

    ReplyDelete
  3. driver Search...

    [...]worth a try - even though we provide external links we aren't responsible for th[...]...

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts