Setting Up ODBC from Microsoft Access to the iSeries

To set up ODBC to transfer data from a Microsoft Access database table to an iSeries file using Fetch-n-Fill:
 
  1. Create the iSeries file:
    1. Enter iSeries DDS specifications.
    2. Compile DDS to create the iSeries file.
  2. Create the Microsoft Access table within the database.
  3. In the ODBC 32-bit driver adminstrator, define the AS/400 DSN:
    1. Open the ODBC 32-bit Administrator.
    2. On the System DSN tab:
      1. Click Add to add new DSN entry.
      2. In the data source, choose Client Access ODBC Driver (32-bit).
      3. Click Finish.
      4. In Data source name, choose a meaningful name.
      5. Choose a meaningful description.
      6. In the System list, choose AS/400 System.
    3. On the Server tab, in Default libraries, enter a list of default iSeries libraries for this DSN entry.
    4. On the Translation tab, click Translate CCSID 65535.
    5. Click OK.
  4. In the ODBC 32-bit driver adminstrator, define the Microsoft Access DSN:
    1. Open the ODBC 32-bit Administrator.
    2. On the System DSN tab:
      1. Click Add to add new DSN entry.
      2. In the data source, choose Microsoft Access Drive (*.mdb).
      3. Click Finish.
      4. In Data source name, choose a meaningful name.
      5. Choose a meaningful description.
      6. In Database, click Select.

        Select Database displays.

        1. Browse to the Microsoft Access database and highlight it.
        2. Click OK to select this database.
    3. Back in ODBC MicroSoft Access Setup, click OK.
  5. Set up the job in Fetch-n-Fill:
    1. Open Fetch-n-Fill.
    2. Create a new job in Fetch-n-Fill.
      1. Right-click New Job and click Properties.
      2. On the General tab:
        1. In Name, choose a meaningful name.
        2. For testing purposes, turn on:
          • Show status while running.
          • Count records before running.
      3. On the Origin tab:
        1. In DSN, choose the Microsoft Access DSN name from step 4.2.4.
        2. Leave User ID and Password blank.
        3. In Table, choose the table name from step 2.
        4. Click Get Field List to retrieve a list of Microsoft Access table fields.
        5. Highlight each field to select and then click Select Field(s).

          Note: To select multiple fields at once, hold the CTRL key while highlighting fields.

      4. On the Destination tab:
        1. In DSN, choose the AS/400 DSN name from step 3.2.4.
        2. In User ID, enter a valid iSeries user name.
        3. In Password, enter the password for User ID.
        4. In Table, choose the iSeries file from step 1. Use the format:

          [iSeries library name].[iSeries file name]

        5. Click Get Field List to retrieve a list of iSeries file fields.
        6. Using the same order as the fields you selected in the Origin tab, highlight each field to select and then click Select Field(s).

          Note: To select multiple fields at once, hold the CTRL key while highlighting fields.

      5. Click OK to save the job information.
    3. Right-click the job and click Run to test the transfer.
Still have questions? We can help. Submit a case to technical support

Last Modified On:
You don't have the appropriate permissions.
No, open a new Support Case
+1 800-328-1000
[email protected]
Infrastructure Protection & Data Security
Managed Security Services
Automation
IBM i

Copyright © 2023 Fortra