Migrating a Microsoft Access Database to Oracle Database 11g

This tutorial shows you how to migrate a Microsoft Access database to Oracle Database 11g using Oracle SQL Developer.

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft Access Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your Microsoft Access database to Oracle.

There are four main steps in the database migration process:

Capture the Source Database
Convert the Captured Database
Generate the Oracle Database
Migrate the Data

In this tutorial, the required scripts for the offline migration have already been generated and modified. If you do not have time to perform this tutorial, you can also view the offline method, click here.

To view the steps for the online method, click here.

Back to Topic List

Before you perform this tutorial, you should:

1.

Install the Oracle Database 10g or later, or Oracle Database XE

2.

Download and unzip Oracle SQL Developer here.

3.

Download and unzip the msaccessmigration.zip file into your working directory (i.e.wkdir)

To create a new database user, perform the following steps:

1.

Open Oracle SQL Developer from the icon on your desktop.

 

2.

Select View > Connections.

 

3.

In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

 

4.

Enter system_orcl in the Connection Name field (or any other name that identifies your connection), system for the Username field, and <your password> for the Password field. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

5.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

6.

The connection is saved and you can see it listed under Connections in the Connections tab.

 

7.

Expand the system_orcl connection.

Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

 

8.

Enter the following code in the SQL Worksheet to create a user for the migration repository.

CREATE  USER MWREP 
IDENTIFIED BY mwrep 
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

 

9.

Run the script , using the "Run Script (F5)" icon.

 

10.

The mwrep user was created successfully.

 

Back to Topic List

To convert the Microsoft Access XML to Oracle, you need to create a repository to store the required repository tables and PL/SQL packages. To do this, perform the following steps:

1.

Before you create the repository, you need to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Note: If this tab is not visible, select View > Connections.

 

2.

Enter mwrep_orcl in the Connection Name field (or any other name that identifies your connection), mwrep for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

3.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

4.

The connection is saved and you can see it listed under Connections in the Connections tab.

 

5.

Right-click the mwrep_orcl connection and select Migration Repository > Associate Migration Repository.

 

6.

A progress window appears.

 

7.

Click Close when the install completes.

 

8.

Click OK.

 

Back to Topic List

The procedure for exporting the Microsoft Access database file to an XML file has been completed for you and the XML file is available in the Capture directory. To view this procedure, click here.

To capture the Microsoft Access exported XML file into Oracle SQL Developer, perform the following steps:

1.

Select Migration > Capture Microsoft Access Exported XML.

 

2.

A Microsoft Access Capture dialog box appears. Click Browse....

 

3.

Browse the Capture directory and select the Northwind.xml file.

 

4.

Click OK.

 

5.

The objects are being captured. When done, click Close.

 

6.

Northwind (Access) is listed in the Captured Models tab. Expand Northwind (Access).

 

7.

Expand Northwind to see the list of objects that were captured.

 

Back to Topic List

It is important to review the conversion preferences at this point. To do so, perform the following steps:

1.

Select Tools > Preferences.

 

2.

Expand Migration and select Identifier Options.

 

3.

Make sure Is Quoted Identifier On is selected. Click OK.

 

Back to Topic List

To convert the captured model to the Oracle model, perform the following steps:

1.

Right-click the captured model Northwind (Access) and select Convert to Oracle Model.

 

2.

The Set Data Map window appears which shows you the Source Data Type and what it will be converted to in the Oracle Model. Click Apply.

 

3.

The conversion is performed. When done, click Close.

 

4.

Expand Converted:Northwind (Access) listed in the Converted Models tab.

 

5.

Expand Northwind to view the converted objects

 

Back to Topic List

To generate the SQL script with DDL statements that will be executed to create the objects in an Oracle Database, perform the following steps:

1.

Right-click Converted:Northwind (Access) and select Generate.

 

2.

The Oracle SQL is being generated. When done, click Close.

 

3.

The SQL from the script is shown. Select system_orcl from the drop-down on the right. icon.

 

4.

Click the Run Script (F5).

 

5.

The results are display from the script execution.

 

6.

Now that your scripts have run successfully, you can create a connection for the Northwind user. Right-click Connection and select New Connection.

 

7.

Enter northwind-migrated_orcl in the Connection Name field (or any other name that identifies your connection), Northwind for the Username and Password fields. Note that the password is case sensitive. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

8.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

9.

Expand the northwind-migrated_orcl connection.

 

10.

Expand Tables.

 

11.

The database tables that were converted to Oracle are listed. Select the EMPLOYEES table.

 

12.

Select the Data tab. Notice that currently there is no data in the table. You will migrate the data later in this tutorial.

 

Back to Topic List

A date format masks can be specified in the preferences so that the Offline Data Move scripts and in particular the Oracle SQL*Loader control files can reference the correct format. To do so, perform the following steps:

1.

Select Tool > Preferences.

 

2.

Expand Migration and select Data Move Options.

 

3.

Make sure the Date Mask is the following.

yyyy-mm-dd HH24:mi:ss.ff3

 

4.

Make sure the Timestamp Mask is the following.

yyyy-mm-dd HH24:mi:ss.ff3

Click OK.

 

Back to Topic List

The migration from Microsoft Access to an Oracle database can be straightforward. But it is worth noting that some objects and syntax are not automatically migrated. Therefore manual intervention is required.

Analysing the Capture Model, identifying the number, type and complexity of objects , can help calculate the estimated time required for manual tasks.

The following Reports can be used with your own task and project estimation.

Estimate time to:

Back to Topic List

The data has already been exported into files from Microsoft Access. To view the steps, click here.

To import the data using the scripts provided, perform the following steps:

1.

Open a DOS command prompt and execute the following commands:

<prompt> cd <location where files are>

<prompt> oracle_ctl

The files are located in the Data directory where you unzipped the files provided in the prerequisites section.

oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.

 

2.

The command executes successfully.

 

3.

Switch to Oracle SQL Developer and Refresh the connection.

 

4.

The data for the table has been loaded successfully.

 

Back to Topic List

Depending on the complexity of the database being migrated, a large part of the migration project could be testing.

Currently Oracle does not provide any tools that can automatically validate a migrated database for you. This phase of the migration should be planned and taken into account before the migration begins.

The following should be part of your project plan

Back to Topic List

Summary

In this tutorial, you learned how to:

Back to Topic List

Place the cursor over this icon to hide all screenshots.