Query Two Databases

[Guest]

Hi,

I am (still) developing with VB 6.0 and MS Access 2000 on Win98-R2.

Is it possible to easily/reliably query two tables that each exist in a
different database, then return the results in a single recordset? Or must I
create a linked-table reference in one of the databases? Or must I do
something else … ?

An app I am working on uses two separate databases. The first is a large
set of “lookup†tables, and data stored there doesn’t change frequently. The
second database is used/updated much more frequently. Here I need to create
a new table that stores keys to a “lookup†table in the first database.

I really don’t want to combine the two databases. And since the dbs are
password-protected, I don’t want to use the table-link approach if I can
avoid it.

Is it possible in MS Access to use the same ADO connection/query to access
tables in two separate dbs?

Thanks,

Joe

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Depends on the databases. If the 2 DBs are MS SQL Server DBs on the
same server you can do a query like this:

SELECT A.*, B.*
FROM databaseA..table1 AS A INNER JOIN dtabaseB..table1 AS B
ON A.<some ID column> = B.<some ID column>
.... etc. ...

If the DBs are both Oracle, I believe you can use the same syntax, since
it is an SQL standard naming convention.

The syntax for indicating a table is

<database name>.<owner name>.<table name>

If the DBs are Access DBs, you can use DAO like this:

SELECT A.*, B.*
FROM table1 IN "C:\My Documents\DatabaseA.mdb" As A
INNER JOIN table2 IN "C:\My Documents\DatabaseB.mdb" As B
ON A.<some ID column> = B.<some ID column>

Perhaps it would work in in ADO, also.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HMwIechKqOuFEgEQKKtACg5n+L2lEg+R0kI3tfPzfL4FR6yS4AoIAG
i86oTFK2wJ6kkgmPwkPrjxbU
=APdA
-----END PGP SIGNATURE-----

 

[Guest]

Hi MGFoster,

Thanks for the idea. Unfortunately, the password on the external db causes
problems. I got the following error message when I ran a test query: “Not a
valid passwordâ€.

I will tinker around some with this idea to see if I can specify the
password in the SQL string.

Joe