DB2 Interfaces with Microsoft Access

When you use Microsoft Access to interface with a DB2 database, you are given two options for the ODBC connection. The first is to allow a link to the database, and the second is to import a table from DB2. Since you need to use ODBC for Access to talk with DB2, you should install the DB2 Client Enabler software.

If you want up-to-date access to data that is constantly changing, you will want to link to the database. If, however, the data doesn't change or you don't need up-to-date access, you can simply import the data. This will give you a copy of the data as it exist at the time of the import. It will never change.

The following information is from Microsoft Access:

Import or link SQL database tables or data from other ODBC data sources

NoteYou can link a table only in a Microsoft Access database, not a Microsoft Access project.

Important You cannot import or link an SQL or other ODBC data source unless you have installed the appropriate driver.

1. Open a database, or switch to the Database window for the open database.
2. To import tables, on the File menu, point to Get External Data, and then click Import.

   To link tables, on the File menu, point to Get External Data, and then click Link Tables.

3. In the Import (or Link) dialog box, in the Files Of Type box, select ODBC Databases ().

   Important: If you link to a file on a local area network, make sure to use a universal naming convention (UNC) path, instead of relying on the drive letter of a mapped network drive in Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.

   The Select Data Source dialog box lists the defined data sources for any ODBC drivers that are installed on your computer. Click either the File Data Source or Machine Data Source tab, and then double-click the ODBC data source that you want to import. To define a new data source for any installed ODBC driver, click New, and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before continuing.

4. If the ODBC data source that you selected requires you to log on, enter your logon ID and password (additional information might also be required), and then click OK.

   Microsoft Access connects to the ODBC data source and displays the list of tables that you can import or link.

5. If you're linking a table, select the Save The Login ID And Password check box to save the information for the table in the current databases that users won't have to enter it. If you leave the check box cleared, all users must enter the logon ID and password every time they open the table in each new session with Microsoft Access. Your SQL database administrator can also choose to disable this check box, requiring all users to enter the logon ID and password each time they connect to the SQL database.
6. Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then click OK.

Notes

• After importing or linking an SQL database table, you can set field properties for the table. If you import a table, you might want to set a primary key for the table.
• If you encounter an error while importing, linking, or using an SQL database table, there might be a problem with your account on the SQL database server or with the database itself. If you can't access an SQL database table, contact the SQL database administrator.
• To edit a linked SQL database table, usually the table must contain a unique index on the server. If you want to edit a table that doesn't have a unique index, or if you want to edit an SQL view, then you can create an index within Microsoft Access that the SQL database isn't aware of. You do that by creating a data-definition query, using the Create Index statement. Be aware, however, that the index must be created on a field or combination of fields in which each value is unique. If the field contains any duplicate values, all updates to the table will fail. To delete the index, use another data-definition query.
• If the structure of an SQL database table changes after you link it, use the Linked Table Manager to refresh the link.
• If you are importing from a Microsoft SQL Server database into an Access Project, consider using Data Transformation Services (DTS), which can be a faster and more flexible way to import and transform data.

Learn More about...

• DB2 - this link will provide access to the online DB2 Online Books.