Anyone know how Access interacts with Oracle?

Post #117,140 by Meerkat 9/8/03 11:00:53 PM Reply	Anyone know how Access interacts with Oracle? Hi, I'm kinda getting yelled at because Access doesn't play nice with Oracle. Go figure. Anyway, I guess that's beside the point... In short I'm trying to find out what queries Access (97) issues to Oracle (9i) when an Access user tries to link to Oracle tables via ODBC. As was requested, access to nearly all the system tables has been revoked (because seeing all those system tables is "confusing", apparently...) And funnily enough, when you do that, Access' 'Link Table' functionality breaks. So far I've been able to determine that when Access tries to build the list of of available tables in Oracle, it queries ALL_OBJECTS and ALL_SYNONYMS. (I should Twikify all this when I have it sorted out...) Anyway. When actually trying to link to the table, it then claims that no such table exists. So I'm trying to figure out what other system tables it hits in its quest to link to Oracle tables. Dead ends that have already been hit: Local ODBC logging doesn't offer any clues. Our DBA says he's unable to turn on any SQL tracing or logging at the Oracle end. MSDN, OTN, and Google have so far not really been my friends. Telling people they're stupid for using Access has only met with some rather icy glares :) I'm just not quick enough on the 'Sessions' tab in Oracle's Enterprise Manager to grab the current SQL that is running when a Link Table request is in process. The Oracle error message returned states Table Does Not Exist, but doesn't tell which table it's trying to query. Has anyone had to deal with this before? If not, could someone at least wish me luck? :-) Many thanks, as always! John. Busy lad.
Post #117,207 by jbrabeck 9/9/03 10:41:24 AM Reply	Since I haven't had to do this, I'll wish you luck!
Post #117,211 by ChrisR 9/9/03 10:45:03 AM Reply	Haven't tried it on Oracle But I had to deny any Access attachments to SQLServer tables. Tends to eat server cycles and lock way too much data. If Access can't play nice with it's own cousin, I doubt it will react well with the competition.
Post #117,285 by Meerkat 9/9/03 7:26:49 PM Reply	Very good point! I'll post a solution if / when it all gets figured out. Or, when folk decide that seeing 'PUBLIC.ALL_OBJECTS' etc no longer gives them the cold pricklies :) If all else fails, I'll end up installing Access and Oracle at home, and sniff network packets as Access tries to establish a link. As for Access not behaving itself when connected to other databases - we know this from painful, repeated experience. My favourite is when there's a query on a ~100million row table. Access usually decides "Hmm... yeah, I think I'll grab all the rows, store them locally, then apply there WHERE clause myself". Whilst Access would benefit from EXPLAIN PLAIN, the command I feel it really needs is JUSTIFY EXISTENCE... John. Ranting lad.
Post #117,983 by Simon_Jester 9/17/03 12:08:50 AM Reply	Well, the All_Tables and All_Synonyms makes sense since it would be getting a data dictionary of all possible tables. Hmm...checked out OTN?
Post #117,990 by Meerkat 9/17/03 3:44:47 AM Reply	The solution Inspired by / adapted from a MetaLink search... In short, fool Access into thinking that each user can only see the tables for the Data Warehouse. Do this by creating a view on SYS.ALL_OBJECTS for each user, which only contains Data Warehouse tables. Roughly, the view would be like "Select * from SYS.ALL_OBJECTS where OWNER='WAREHOUSE' ". So, when the user links tables through Access, Access queries the ALL_OBJECTS view, and only sees Data Warehouse tables. Nifty, huh? So now everyone's happy :) Thanks to all for your input. John. Busy lad.
Post #118,000 by andread 9/17/03 9:25:26 AM Reply	Re: The solution Here the database gurus have Access reports that come from Oracle but can be used by desktop Access users A Play I Some Music w/ Papa Andy Saturday 8 PM - 11 PM ET All Night Rewind 11 PM - 5 PM Reggae, African and Caribbean Music [link\|http://wxxe.org\|Tune In]

Welcome to IWETHEY!