How to Connect to Oracle Database Using Ms Access

Featured Database Articles

Oracle

Posted May 26, 2004

Connecting with Oracle: Accessing Oracle via Access and Excel

By Steve Callan

Connecting with Oracle can be quite a challenge for new users and DBA's. Once you've learned the ins and outs of configuration files and Oracle Net, connecting via SQL*Plus or another Oracle application is a piece of cake. However, as a new Oracle DBA in a small to medium sized business where all of your users are Access and Excel wizards, should you be expected to teach all of your Office users how to become Oracle experts when you yourself barely know what to do? So far, you have been armed with the Oracle 10g 2 Day DBA course because your business has taken the plunge into the Oracle world.

Will your Access and Excel users be able to view data within an Oracle database right now, or do they have to wait for a SQL or SQL*Plus class next month? Fortunately, for you and your users, giving them the ability to access an Oracle database using either Access or Excel is easy to do. It takes all of three minutes to set up the link or connection. Moreover, even for DBA's in larger Oracle shops, knowing how to expose table data via these applications is a useful skill. Because Access and Excel can both be used to view table data within an Oracle database, the tools Oracle lacks with respect to data manipulation and analysis are more than made up for when using Access or Excel. With Access, users can also insert, delete and update data (Excel is view only).

What is the key to making this connectivity happen? Open Database Connectivity, or ODBC as it is commonly known, is your key to success. First, there is a Microsoft-Oracle connection definition step, and then Access and Excel each use similar steps to get external data. The following steps and screenshots are based on a PC using XP Professional.

Setting up the data source definition

If you expand the Start>Programs>Your Oracle Home program>Configuration and Migration Tools path, you will see a utility named Microsoft ODBC Administrator. Launch that utility to begin the process. If you have a fairly standard PC from any major vendor, it will already have definitions for dBASE, Excel and Access (note that these are all Microsoft products). Not to worry, because adding an Oracle database as a source is just a few clicks away. Shown below is a typical pre-Oracle view of the window.

Click on the Add button to add a new data source. In the window shown below, simply enter the name of your Oracle database, give it a description, enter the name of the service and provide a user ID (I added Scott's password so as not to be prompted for it down the road). If you want to test the connectivity, click on the Test Connection button (good idea).

If your connection test was successful, you will see the following:

After clicking OK on the Oracle ODBC Driver Configuration window, you will see your Oracle database listed in the ODBC Data Source Administrator window.

Now you are ready to make a link in Access.

Go to page: 1  2  3  Next

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic	By	Replies	Updated
Oracle Data Mining: Classification	jan.hasller	0	July 5th, 07:19 AM
Find duplicates - Unique IDs	Lava	5	July 2nd, 08:30 AM
no matching unique or primary key	rcanter	1	April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger	Gladiator	3	February 29th, 06:01 PM

How to Connect to Oracle Database Using Ms Access

Source: https://www.databasejournal.com/features/oracle/article.php/3358411/Connecting-with-Oracle-Accessing-Oracle-via-Access-and-Excel.htm

Share this post