Pulling data from Oracle tables with Access

[robojeff]

I have been away from Access for awhile so I apologize in advance if I sound like a newbie
or posted this in the wrong area and I am counting on you to set me straight if I am wrong...

I am trying to pull some data from a couple of Oracle tables and the Oracle admin has set up
an account for me and wrote an Oracle query to pull the data with an OBIEE connection,
but I would rather put together a query in Access to pull and manage this data.

The problem that I am having is that when I go to create a linked table with the
external ODBC database and log into the Oracle client with my user name and password
(which works with the OBIEE connection), one of the main tables does not appear in the link tables
window.

Is this a problem with Access or the way that the administrator set up the account tables that
I am attempting to connect with in Access?

I can see other linked tables but also need one table that is not being displayed in my link tables window.

I am using Microsoft Access 14.0.7162,.5000 (32-bit)

Thanks

 

[kray4660]

If you can see the other tables. I would suspect it is a rights issue. But then I never attempted (or needed to) link to Oracle tables.

 

[robojeff]

Thanks for the reply...

The funny thing is that the same user and password can access all of the tables (including he hidden ones) in Oracle.

I did find a Microsoft knowledge base doc relating to Access 2000 (

https://support.microsoft.com/en-us/kb/325835)

explaining how to modify the registry on my PC but this didn't help...

 

[robojeff]

I asked the Oracle admin if he could create a user with less table access (only the tables that I need)
as the problem appears to be too many tables for the current user login that I use which is more data than
Access's link table window can handle so that not all of the tables that I need are appearing.

He is not willing to do this due his concerns of Access affecting his Oracle system's performance but his
OBIEE query exports a CSV or excel spreadsheet. So as a work around, maybe I can create a spreadsheet that
I can pull the data that I need and dump this into one of my tables.

What is the best way to write a query to pull data from the excel spreadsheet and are there any good examples
of this out there that anyone can point me to?

Thank you

 

[Andrzejek]

If you are only reading the data from Oracle tables (no Updates, Deletes, or Inserts), you may ask your admin to create a view/views in Oracle (they are called 'queries' in Access) to which you can have access. That approach should not create a performance issues in Oracle.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[robojeff]

Thanks Andy-

The admin made it clear to me that he doesn't want to open the door to anyone using Access to pull data from his Oracle system
even with read only access, so the only option that I have is to pull the data into a spreadsheet with his OBIEE query and then
grab the data with my Access database...

Seems really inefficient but he controls the Oracle database...

 

[Andrzejek]

There are certain levels of control that you cannot by-pass. I understand it. Sometimes that depends on the business rules, sometimes on the people, and some of the times on one person with the reason: “Because I said so”.
In our organization, an Admin is ‘just’ an Admin, and he/she does NOT own the data. You may want to check with whoever owns the data you need and talk to them. Pulling the data with a view, or into a spreadsheet, text file, csv file, xml file – it is still accessing the data you need.

I would be very worry about going thru Excel because of Excel’s ‘interpretation’ of some data.


Have fun.

---- Andy

There is a great need for a sarcasm font.