File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes how to access MSAccess and Oracle in one query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "how to access MSAccess and Oracle in one query" Watch "how to access MSAccess and Oracle in one query" New topic

how to access MSAccess and Oracle in one query

Seshi Reddy

Joined: Mar 05, 2006
Posts: 1
In one scenario i want to display the data from two databases i.e, they r different database(MSAccess and Oracle). Im trying to use .properties in Java to load the database connections. pl reply ASAP.

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
Welcome to JavaRanch.

While with some DBs it is possible to retrieve data from two of them in the same query, there is no way to do that if you have two different kinds of databases. You will have to write two queries. It is possible to come up with a transaction that spans two different kinds of databases, but not if one of them is Access.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Actually, some databases allow you to configure a "gateway" or "bridge" or "link" to another database through it, sometimes even a different type of database. With such a thing configured, it then becomes possible to have a single query get data from 2 databases at once with a query in the style of:

However, this can create shall we say interesting performance problems and I wouldn't recommend it as a general solution.

Oracle asserts that it can be configured somehow for connectivity to an MS Access DB, here (Generic Connectivity):

However, I don't know how to do it, or even where in the documentation to start looking, though...
I agree. Here's the link:
subject: how to access MSAccess and Oracle in one query
It's not a secret anymore!