Jumbabox

Ever wanted to get a query or table which you have in Access exported into a table in an Oracle database? Read on!

Related : Connecting an Excel Pivot Table with an Oracle database

This tutorial assumes you have an Oracle client installed on your PC already, this can also work with Oracle 10g XE if you download the client side for XE (click here to download the Oracle 10g XE client ~30mb).

Step 1 to 3 were directly taken from my post on how to connect an Excel Pivot Table with an Oracle database

Step 1 - The Data Sources (ODBC) Administrator

Click on Start > Settings > Control Panel

Once the control panel dialog box has opened, locate and double click on Administrative Tools.

Then click on Data Sources (ODBC)

You should now see the ODBC dialog box.

Step 2 - Creating a new ODBC Connection

We will need to stay in the User DSN tab, click on the Add… button on the left hand side of the ODBC Data Source Administrator, a new dialog should pop up and will list all available drivers on your computer.

Browse to the bottom of the list and you should find an Oracle driver, in this case I’m using the Oracle 9 driver.

Once you have found your Oracle driver hit the Finish button and another new dialog box will appear with the configuration for your driver’s settings (seen below).

Step 3 - Configure your Oracle driver

Firstly, give your new data source a name, in this example I will be calling mine JUMBABOX_ORCL.

Then enter a description about your data source.

The next step is to choose your TNS Service Name, this should appear in the drop down box. If you can not see your TNS Service Name in the drop down box, type it in manually which is what I did. If nothing is in the drop down box, you can double check your TNS Service Name’s via the tnsnames.ora file (do a simple search in Windows to find it).

In this example my TNS Service Name is EARTH_ORCL_JB.

Next, put in your User ID, in this example it is EARTH_DBA and click Test Connection.

A new dialog box will pop up asking for a password, enter your password for this data source and click OK.

If successful you should see this message box.

If you get this error, you have no entry for your specified TNS Service Name in your tnsnames.ora file.

If you get this error, you have an entry in your tnsnames.ora file for your TNS Service Name however there are problems connecting to your data source.

After you have a successful test, click OK and now your new ODBC Data Source connection should be visible under the USER DNS tab.

Step 4 - Exporting from Access to Oracle

In this example I will be exporting a Query.

Before we continue I want to make sure the query I will be exporting has the right data types. As it isn’t the query which stores the data type I will have to open up the table it is based on.

To view the data types for the columns of the table, right click on the table object and click on Design View.

The Design View for the table should open up, as you can see there are different data types set for each column, these will be replicated in the table created on the Oracle database.

Close down Design View and then right click on the object which you want to export again and then click the Export… option.

A dialog box will pop up asking you where you would like to export the database object.

From the Save as type: drop down box, select the ODBC Databases() option.

Right after clicking ODBC Databases() an Export dialog box will pop up. This step is crucial as it will define the name of the table which will be created in your Oracle database, I will name mine TBL_SAMPLE. Once you are done click the OK button.

If you try to create a table or export to a table name which already exists, you will receive this error:

After clicking the OK button a Select data source dialog box will pop up, click on the Machine Data Source tab and click on the ODBC data source which is already or just set up. As you can see my one was called JUMBABOX_ORCL. Once you have selected your ODBC data source click the OK button.

After clicking the OK button an Oracle ODBC Driver Connect dialog box will pop up asking for the password to your ODBC connection, type in your password and click the OK button.

Right after you click the OK button you will notice a Export progress bar in the bottom left hand corner of Access. You will not be notified of when the export is complete.

I have noticed when using this method if I’m exporting a large data sets that the progress bar will look complete but it will still be processing, best to check out your Oracle Management Console to see the progress of the query.

After opening up SQL Developer, I can see that the table has been created and the data has been inserted.

Done! You have just exported a query or table from Access to a table in an Oracle database.

Behind the Scenes

If you are interested this was the generated CREATE statement:

CREATE TABLE "EARTH_DBA"."TBL_SAMPLE"
   (	"CUSTOMER_ID" NUMBER(9,0),
	"CUSTOMER_NAME" VARCHAR2(132 BYTE),
	"MAIN_CONTACT_SUB_ID" NUMBER(4,0),
	"CONTACT_NAME" VARCHAR2(132 BYTE),
	"CONTACT_TN" VARCHAR2(12 BYTE),
	"CONTACT_ADDRESS_ID" NUMBER(9,0),
	"STREET_NO" VARCHAR2(8 BYTE),
	"STREET_NAME" VARCHAR2(50 BYTE),
	"STREET_NAME_TYPE" VARCHAR2(10 BYTE),
	"STATE_CODE" VARCHAR2(3 BYTE),
	"POST_CODE_PC4" VARCHAR2(4 BYTE),
	"LOCALITY_NAME" VARCHAR2(45 BYTE),
	"STREET_NUM_PREFIX" VARCHAR2(30 BYTE),
	"STREET_DIRECTIONAL" VARCHAR2(10 BYTE)
   )

When we compare the data types in Access to Oracle I noticed that:

• Text = VARCHAR2
• Number = NUMBER

Another interesting thing is to notice that the CREATE statement looks at each column and works out the largest length of a field and sets this up accordingly.
For example: MAIN_CONTACT_SUB_ID longest field was 4 characters long, so the data type length was set to 4.

If you have any problems or suggestions please comment!

Enjoy!