Oracle and Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AP - 05 Jun 2006 17:22 GMT

Hello
I am looking for the MOST efficient way to pull data from an Oracle
backend. By most efficient I mean will have the least impact on the
LAN. I am trying to pull specific data by date and put it into a local
table for anlaysis.

Not sure if I should just link to the backend and run a single query to
fill my analysis local table, or if I should use ADO? If I use ADO I am
not positive what the best way to get the data from an ADO recordset
into an Access table would be.
Thanks in advance.

Reply to this Message

MGFoster - 05 Jun 2006 21:01 GMT

If you're transferring data from an Oracle DB to an Access DB you'll
always be affecting the LAN. The most efficient way is to use an SQL
Pass Through query (SPT query). Read the Access Help on pass thru queries.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hello
> I am looking for the MOST efficient way to pull data from an Oracle
[quoted text clipped - 7 lines]
> into an Access table would be.
> Thanks in advance.

Reply to this Message

Tim Marshall - 06 Jun 2006 06:31 GMT

> Not sure if I should just link to the backend and run a single query to
> fill my analysis local table, or if I should use ADO? If I use ADO I am
> not positive what the best way to get the data from an ADO recordset
> into an Access table would be.

ADO blows with Access and Oracle. You can't create reports in an mdb
with it. I use DAO and pass through queries.

I used to do what you're suggesting, creating a "snapshot" of the Oracle
data. However, at the time, I was more confortable with Jet and the
wonderful Access interface. If you're reasonably proficient in Oracle
SQLese, I'd use PTQs and not bother with filling a local table.

I wouldn't worry about the LAN. Oracle is designed to be used in a
client server environment.

Using ODBC linked tables (like Access tabledefs) is also a perfectly
reasonable approach, though depending on the design of the Oracle
database (the one I work on looks like it was designed by a drunken
spreadsheet user) you can run into serious limits in query design this
way that PTQs will not encounter.

Signature

Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Reply to this Message