IBM Support

Microsoft Access - Connecting to an ODBC Data Source

Troubleshooting


Problem

This document provides information about connecting Microsoft® Access to Db2 on i using ODBC.

Resolving The Problem

To connect to Db2 using the IBM i Access Client Solutions ODBC driver using Microsoft® Access, do the following:

Note: The following screen shots are taken from Access 365. Different Access versions may have subtle differences.
  1. If you haven't already done so, create a data source using the ODBC Administration tool in Windows.
  2. Start Microsoft Access, select Blank Database and name it to create a new database or select an existing one to work with.
  3. Click the 'External Data' ribbon, select 'New Data Source' > 'From Other Sources' > 'ODBC Database'
    image-20241211165837-1
  4. Select the option to import the data source if you want a one-time local copy of the data or the option to link to the data source to use real time data.  Click OK
    image-20241211170155-2
  5. You are almost certainly going to be using a data source that was saved to the Windows registry instead of a file data source.  So select the 'Machine Data Sources' tab and select the ODBC data source you have defined to connect to IBM i.
    image-20241211173858-3
  6. Select the table or tables that you want to link then click OK.
    image-20241211174412-5
  7. The table I selected does not have a primary key defined. When no primary key is defined for the table, Access prompts you to select one or more columns to be used as a unique key.  Note that they are not actually creating a unique key constraint on the database.  In my example, I've selected all three of the tables columns.
    image-20241211174439-6
  8. Once linked, the table shows up in your Access database.  You can open it by double-clicking the table or right-clicking it and selecting 'Open'.
    image-20241211174718-7
  9. The data is displayed in a data grid.  Access allows you to edit, delete, or add records only if you have a unique index defined for the file and the table is journaled.  If you do not select a unique key rule when you link the table Access will not allow any changes to the data.  If the table is not journaled, you will receive errors SQL7008 when you attempt any change to the table.
If you intend to update or modify a table with Microsoft Access, it is best to create a unique key constraint in Db2 rather than rely on the behavior of Microsoft Access.  Because it is not really creating a constraint, you can get some very strange results displayed by Access that are not correct.  For example, I linked the table in the example above using only COL1 as my unique key and then added a new record to it with the value 'Today' in COL1, '12/11/2024' in COL2 and left COL3 blank.  It actually inserted those values but this is what is displayed by Access:
image-20241211180603-8
Note that it is treating COL1 as a unique key so the 3rd and 4th rows that have null values in COL1 are both displaying the data that is actually only in the 3rd row.  While it inserted the correct date in COL2 what it displays matches the value for row 1.  Here is the actual data that resides in the table:
image-20241211180759-9

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001gDAAAY","label":"Data Access-\u003EODBC"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

7871148

Document Information

Modified date:
11 December 2024

UID

nas8N1010272

Page Feedback