INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pulling data from Oracle tables with Access

Pulling data from Oracle tables with Access

(OP)
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

RE: Pulling data from Oracle tables with Access

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.

RE: Pulling data from Oracle tables with Access

(OP)
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...



RE: Pulling data from Oracle tables with Access

(OP)
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

RE: Pulling data from Oracle tables with Access

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.

RE: Pulling data from Oracle tables with Access

(OP)
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...

RE: Pulling data from Oracle tables with Access

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close