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).
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.
Configuring Access to connect to Oracle
Configuring Access is just about as simple as
the previous steps. In the next example, I
created a new Access database and used it to
read Scott's EMP table. I also updated data and
added a new employee.
As a side note, Oracle has stated that the Scott
schema has become a deprecated feature. It seems
that future releases will be using the sample
schemas (HR, SH, etc.) you see installed when
using DBCA. If you want to keep the Scott
schema, you can get its creation scripts from
one of two places: ORACLE_HOME\rdbms\admin\utlsampl.sql
and \sqlplus\demo\demobld.sql.
Start Access and create a new database. Go to
File>External Data>Link Tables. In the Link
window, you will have to scroll down the list
shown in the Files of Type field. Select ODBC
Database as shown in the screenshot.
Once you have done that, you will be prompted to
select a data source. Switch tab views to the
Machine Data Source tab. Recognize the items in
that list? You should, because it is the same as
what you previously saw in the ODBC Data Source
Administrator window.
After highlighting your Oracle database data
source, click OK. You will be presented with a
list of tables. You can control-click on tables
to add more than one at a time. Shown below is
the area in the list where Scott's tables reside
(somewhat towards the bottom of the list).
I selected BONUS, EMP, DEPT and SALGRADE. After
selecting the tables, you will be ready to
access them. Highlight one (EMP, in this case)
and click Open.
A simple query in SQL*Plus confirms the update
(LOOK HERE was typed in later). That was easy,
but what if I had made a mistake? For example,
what happens if I create a new employee, but
violate a constraint? In the screenshot shown
below, I attempted to insert a new employee
named Linda Cole, but I used an existing empno.
What does Access tell me about what happened?
