[dba-Tech] Access/Oracle

Hi Ed,

Most certainly it is possible. I access Oracle data tables from Access
every day. Write applications for others to do it too.

You need to have an Oracle Database login. This may be an issue as
that allows you to loginto the database directly whereas with
Discoverer you are only given the access that is defined for that
tool. I use a seperate Oracle Database login from my regular one - one
that has Read Only access to the specific tables that I need to get
into in Access. With this login you can also use regular Oracle SQL to
query the Oracle database and output the results either to screen or
to a text file. You would do this through a tool like SQL Plus, SQL
Navigotor or TOAD. If you are using one of these tools you maybe
already have a Oracle Database Login. If you don't perhaps you don't
have the login you need. In our shop we also use Oracle Applications
and everybody has an Oracle Applications login, but that is not the
same as an Oracle Database Login and whenever someone needs one of
those to be set up the computer support folks seem to be confused.....
Our Database Administrators have to set up the Oracle Database Login.

Anyway, once you have a login, you will need to set up an ODBC Data
Source. You do this in Settings/Control Panel/Administrative
Tools/Data Sources (ODBC).

I have had good luck using either the Oracle ODBC Driver or the
Microsoft one. I have been able to access either Oracle 8i and 9i
format databases without problems

I always set mine up as a SYSTEM DSN although someone else here used
FILE DSN for a while and that worked too. Confused me quite a lot
though wo when I was in that ap a while back I switched it back to MY
WAY ;-)

Here in order to make it work something called SQLNET has to have been
installed on the system too. That is a component that is used by all
those above mentioned SQL tools. And you need one magic file called a
TNSNAMES.ORA file that contains the list of the Oracle DATABASES and
where they are located on your network. In our case this is set up
when the SQLNET is set up but sometimes needs modifications to get at
newly added databases.

It would be good if you could get to be buddies with one of the
Database Admins cause they know all this stuff like the back of their
hands and they can help you out when you get stuck.

I think the biggest hurdle for you will be getting that database
login. If you don't need to update anything but only need to read
data, asking for READONLY access will often calm the data police a bit
but even with that they may need to restrict you to a finite list of
tables. - OH, ONE MORE THING. In our Oracle installation, we have SO
MANY TABLES - that it confused Access when I got to the point of
trying to link to the tables in my database and NONE came up on the
selection list. What we determined was that we needed to use a login
that had visiblity to a shorter list of tables for it to work. That
was originally why we set up a special Access into Oracle database
login. Since that time because of SOX, we have had to set up custom
logins for each Access into Oracle user that only gives them visiblity
to the tables they need to see in their application. If they need
additional visibility then the DBA has to modify their list before
they can see it. Our Oracle Applications database has more than 10K
tables in it so hopefully that issue wouldn't apply to everyone that
would have a smaller database.

This all works pretty well for me. The response can be good. It
depends on what you are querying and if you are using the indexes to
join on etc. We have some 60 million record tables in our system that
I can get (snap your fingers) kind of response on if I do it right. Or
it can take all day if I don't.

Let me know if I can answer any questions.

Gary

On 1/25/06, Tesiny, Ed <EdTesiny at oasas.state.ny.us> wrote:
> To access our agency's data we have to use this %$#@& Oracle tool called
> Discoverer.  Could one access Oracle tables using Access as a front end
> through an ODBC connection.  If so, would it be a big deal to set this
> up.  The last time I asked, the IT answer was we can't do that.  Never
> got a why we can't do that???  Anyway, that person is now gone and I'm
> wondering if I should give it another try.
> Any thoughts/advice appreciated.
> Ed
>
Gary Kjos
garykjos at gmail.com