[dba-Tech] Access/Oracle

Tesiny, Ed EdTesiny at oasas.state.ny.us
Wed Jan 25 13:27:52 CST 2006


Many Thanks,
Gary, Martin, John, Marty, Jim

Thanks for all the info., now I can fill out paperwork to get this
approved.  It would make quite a few people happy, Discoverer just
doesn't make it.
Ed

Ed Tesiny
EdTesiny at oasas.state.ny.us
 

> -----Original Message-----
> From: dba-tech-bounces at databaseadvisors.com 
> [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
> Sent: Wednesday, January 25, 2006 1:34 PM
> To: Discussion of Hardware and Software issues
> Subject: Re: [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
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
> 



More information about the dba-Tech mailing list