Trying to connect to Oracle and Access

[gleeb]

I am having the hardest time trying to pull related records from an oracle database and an access database. I tried at first to link the oracle table inside of Access and that worked for a while. Now I am getting timeout problems with ODBC.

Preferrably without cycling thru one recordset to read the other is there anyway to open multiple connections and select from a table in each?


Here is an example select:
"SELECT AccessTbl.mbr_no, AccessTbl.MRN, OracleTbl.COLLECTION_DATE, OracleTbl.TEST, " & _
" FROM AccessTbl LEFT JOIN OracleTbl ON AccessTbl.Make8 = OracleTbl.MRN " & _
"WHERE ..."

Thanks

 

[bjd4jc]

Is there anyway that you can setup something like a linked server in oracale to link the Access DB? As far as I know there isn't a way for you to join an access and oracle table like you are wanting to do.

I would check to see if you can link from Oracle to Access and then use SP's on oracle to do your joins.

 

[rsinj]

In the Oracle ODBC driver (if you are using a current version), there is a checkbox for Enable Query Timeout - which is checked by default. Go to your DSN configuration and uncheck the option.

As far as I know there isn't a way for you to join an access and oracle table like you are wanting to do.

This is exactly what you want to use Access to do - you link the Oracle table in Access and then you can join with any other table in that Access database - whether it is physically in that database, or some externally linked database. Obviously there will be performance issues, but it does work. I join between Access, Oracle, Rdb, and SQL Server all the time - it works fine.

 

[bjd4jc]

The unfortunate part, IMO, about this is that you must use Jet to accomplish this task.