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.
