Source of Data Files?

[ryguy7272]

I am trying to find the source of a specific data file...

I often use an Oracle DB, which is accessible via a web browser. After
logging into the DB, I can make a few selections for the specific data sets
that I want to view, or I can see a saved data set. Anyway, all of this is
ok, but I was hoping to find a more direct way of accessing the data that I
need (probably the saved data set). By clicking on Data > Import External
Data > New Database Query, I can see tabs that read Databases, Queries, and
OLAP Cubes. I am pretty sure Databases will give me access to this Oracle
DB, but I’m not positive. In any event, I don’t know where to search for the
Database once I click the ‘Browse’ button. Is there a way to see the source
of the Oracle BD, which now is only viewable through the web browser? I’d
appreciate any help I can get with this. Eventually I’d like to record a
small macro to have the import done automatically, once the link between
Excel and Oracle is established.

Thanks for the assistance!!
Ryan--

 

[Jim Thomlinson]

Everything depends on how your database is set up. When you select
Data > Import External Data > New Database Query
You should see Oracle listed as one of the database options on the Databases
tab. At this point you are accessing the ODBC connection to the oracle
Databases. When you do this you will probably be presented with a list of
DSN's (data server names). Now you need to know on where the tables you want
to access live. Once you select that server you will be shown a list of the
tables on that server. Select the appropriate table and MS Query will open
up. You can now add other tables and criteria and such to generate the data
set you want. The data set can be returned directly to XL or to a pivot
table. If you send the data set to a pivot table you are not limited to
65,536 records.

 

[ryguy7272]

Good advice! I know the Pivot Table loophole; you can probably have over a
million rows in the database, and squeeze it into a Pivot Table as long as
the the Pivot Table doesn't exceed the 65k (approx.) limit. Then problem is
that I can't find the ODBC connection...

If I go into Control Panel > Administrative Tools > Data Sources
(ODBC)...how do I find it in there? I don't know what the path to the DB is.
Can I get it from the web browser...somehow?

Thanks so much,
Ryan--

 

[Tim Williams]

As an IT person I would suggest asking your IT person for assistance.
You'll need a username and password to query the data, and they may or may
not be willing to give you "direct" access to the database.

You'll also need the Oracle client package installed on your local machine.

Tim

 

[ryguy7272]

You pretty much hit the nail on the head Tim; the IT people aren't really
interested in providing "direct" access for the finance people. I thought I
could come up with a workaround, but it doesn't seem like it is going to
happen. Thanks for confirming my suspicions.

Regards,
Ryan--