Oracle and Access

[AP]

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.

[MGFoster]

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.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
AP wrote:

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.

[Tim Marshall]

AP wrote:

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.

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