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.
Once the Access Options dialog is open, then go to the Navigation button.
After clicking on the navigation button, the Navigation Options dialog will open. On this dialog you want to enable “Show System Objects”.
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.
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.
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.
Once connected to the MS Access database from SQL Developer, you will see the connection and tables that are in the database.
From here, you can see all the tables in the database by expanding the TABLE category of the connection.
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.
Next perform a right-click on the highlighted tables. You will see an option for Copy to Oracle. Click this option.
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.
At this point, SQL Developer is copying all the metadata and data over to my Oracle 12c PDB under the user SCOUT.
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.
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:
Current Oracle Certs
Bobby Curtis
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.
Follow if you like:
Some women still may need other help in the form of supplements or prescription medications and recent studies have shown that Black Cohosh is a viable natural treatment option to help induce ovulation dapoxetine for premature
Participants provided a valid e- mail address and their Civil Personal Registration CPR number priligy dapoxetine M32L and or S92A, in the variable light domain