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 >