9 Microsoft Access

This chapter describes how to work with Microsoft Access in Oracle Data Integrator.

This chapter includes the following sections:

9.1 Introduction

Oracle Data Integrator (ODI) seamlessly integrates data in a Microsoft Access database. Oracle Data Integrator features are designed to work best with Microsoft Access, including mappings.

9.2 Concepts

The Microsoft Access concepts map the Oracle Data Integrator concepts as follows: An Microsoft Access database corresponds to a data server in Oracle Data Integrator. Within this server, a schema maps to an Oracle Data Integrator physical schema.

Oracle Data Integrator uses Open Database Connectivity (ODBC) to connect to a Microsoft Access database.

9.3 Knowledge Modules

Oracle Data Integrator provides the IKM Access Incremental Update for handling Microsoft Access data. This IKM integrates data in a Microsoft Access target table in incremental update mode.

The IKM Access Incremental Update creates a temporary staging table to stage the data flow and compares its content to the target table to identify the records to insert and the records to update. It also allows performing data integrity check by invoking the CKM.

Consider using this KM if you plan to load your Microsoft Access target table to insert missing records and to update existing ones.

To use this IKM, the staging area must be on the same data server as the target.

This KM uses Microsoft Access specific features. It is also possible to use the generic SQL KMs with the Microsoft Access database. See Chapter 4, "Generic SQL" for more information.


When reverse engineering MS Access, primary keys are not retrieved. Primary key constraints have to be added manually to the datastores for IKM Access Incremental Update to work correctly.

9.4 Specific Requirements

There are no specific requirements for using Microsoft Access in Oracle Data Integrator.