Import Oracle data into Access through ODBC

You can access Oracle data from Microsoft Access by importing the data into a database or creating a linked table.

When you import Oracle data into Access, it creates a one-time copy of the source data. Changes made to the source data won’t be reflected in Access.

When you link to Oracle data from Access by creating a linked table, Access creates a table that will maintain a link to the source data and changes to data in Access and Oracle will be synchronized through a two-way connection. For more information on creating a linked table to Oracle, see Create a linked table to Oracle data in Access.

Import Oracle data into Access

1. Select Open to open an existing database, or select New > Blank database to create a new database.

Open your Microsoft Access database

2. Select External Data > New Data Source > From Other Sources > ODBC Database.

Select ODBC Database by expanding the New Data Source dropdown menu

3. Select Import the source data into a new table in the current database, then click OK.

Select Import the source data into a new table in the current database

4. Select Machine Data Source, then select your DSN and click OK.

Select your DSN

5. Select the table you want to import data from, then click OK.

Select the tables for import

A success message confirms a successful data import.

Select the Save import steps checkbox in the Save Import Steps dialog

Tip

Optional: Save the import steps for repetitive tasks. For more information, see Save the import steps.

6. Click Close.

The imported table appears under Tables on the left pane.

7. Double-click the table to view the data.

The imported table appeared in the Tables navigation pane displaying its contents

Save the import steps

1. Select Save import steps.

Select the Save import steps checkbox in the Save Import Steps dialog

2. In the Save as field, enter a name of the specification.

3. Optional: Select Create Outlook Task to set a task reminder for the data import.

4. Click Save Import.

The imported table appears under Tables on the left pane.

5. Double-click the table to view the data.

The imported table appeared in the Tables navigation pane displaying its contents