Open and Migrate Microsoft Access in Oracle SQL Developer 4

For many people getting start with databases revolve around using Microsoft Access (MS Access). MS Access is an entry level “database” (if you can call it a database) that Microsoft has been putting out for years. Often people want to move older MS Access “databases” into enterprise databases as they become reliant on the information stored in them. Oracle has recognized this and has enabled Oracle SQL Developer to interact with MS Access and allow for a quick copy of data from MS Access to Oracle.

I have been a baseball fan for as long as I can remember; however, I don’t dwell on stats and win-lose records. I honestly just like to watch the game and watch my boys learn the game at a completive level. With this in mind I went looking for baseball stats that I can put into a database and use for demo purposes. What I found was an MS Access “database” full of information from 1996 up through 2013 thanks to Sean Lahman (here).

Now that I have data I want for testing, I really want to test it in Oracle! Using Oracle SQL Developer I’m able to access the data stored in MS Access and with a right-click of the mouse move the data into Oracle. Sounds simple, right!?  Let’s take a look.

The tools I’m using to do this migration are:

  • Microsoft Access 2010
  • Oracle SQL Developer 4.0  (4.0.1.14.48 w/ JDK 1.7.0_51)
  • Oracle Database 12c (12.1.0.1)

Setup Microsoft Access

In order to access the data in a MS Access “database”, you need to enable the viewing of system objects in Access. In MS Access 2010 this can be accomplished by doing the following once the MS Access database is open.

Open the options for the database.

clip_image002

Once the Access Options dialog is open, then go to the Navigation button.

image

After clicking on the navigation button, the Navigation Options dialog will open. On this dialog you want to enable “Show System Objects”.

image

After enabling “Show System Objects”, click OK all the way out to the “database”. You will notice in the All Access Objects tree there are some system tables that appear to be greyed out.

clip_image007

These are the system tables that Oracle SQL Developer needs access to in order to connect.

Connect to MS Access from SQL Developer

To setup a connection to MS Access in Oracle SQL Developer, is just like setting up a connection for Oracle. From the Connections dialog, click on the green plus sign. This will open the connections dialog box.

clip_image008

You will see a tab that says Access. Click on the tab to open up the dialog to use an MS Access MDB file. Use the Browse button to locate the MDB and give it a connection name. The dot in the password field is just there upon connection. Username is not needed since connections to MS Access is as Admin by default.

clip_image010

Once connected to the MS Access database from SQL Developer, you will see the connection and tables that are in the database.

clip_image011

From here, you can see all the tables in the database by expanding the TABLE category of the connection.

clip_image013

With the connection to MS Access set and usable, I wanted to move all these tables into an Oracle Database 12c for testing.

Quickly moving data to Oracle

All the baseball data can be quickly moved to Oracle in a few simple steps. No, this process does not involve using the migration features of SQL Developer; instead it is a simple right-click.

Start by highlighting one or more tables you would like to copy to Oracle.

clip_image014

Next perform a right-click on the highlighted tables. You will see an option for Copy to Oracle. Click this option.

clip_image016

After clicking Copy To Oracle, a connection dialog will open. For my Oracle connection, I’m connecting to a PDB with a local user named SCOUT. Click Apply.

clip_image018

At this point, SQL Developer is copying all the metadata and data over to my Oracle 12c PDB under the user SCOUT.

clip_image019

When the process is done copying, I can verify that all the tables are there by looking at my Oracle connection pane and opening the Tables node on the tree.

image

Now I have all the baseball data I want to play with loaded into Oracle Database 12c (PDB). Let the fun times begin!

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

Please follow and like:
Comments
  • When I try to connect to MS Access database from sql developer using above method I get error
    Status : Failure – Test failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”
    any inputs ?

Comments are closed.

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.