create 1 query from two databases

[make me rain]

with VB6 (ado) + Access03;
-------------------------
how can i generate a query using two tables from two different data bases ( one is .mde and another .mdb)

(1) i opened the connection and recordsets of the both the
data bases
(2) confusion from here on wards
i want to inner join the .mde1 table with .mdb2 table
and extract some sorted records
any advice please.

[si_the_geek]

Two different connections are no good - because queries run against just one connection.

What recordset(s) you have open already are irrelevant, as they cannot be used as the source for more queries.


There are two ways I can think of to do what you want.

The first is to open one of the databases and add Linked Tables, which allow you to treat the tables of the other database as if they are in the same database (and you get to choose what to call them).

The other is to use an SQL statement that includes information to link to the other database. There is info about doing that for Access in the "SQL" section of our Database Development FAQs/Tutorials (at the top of this forum)

[make me rain]

excuse me please for missing info;
my data bases are on two computers connected via LAN.
sql2000 is running on one system;
.....................
the .mde database is in a shared folder

(a) i am failed to connect to .mde file on server machine ( LAN )kindly tell me the connection string

(b) how to link the tables using ADO. and more over other
data base is a .mde file

[si_the_geek]

a) See the "connection strings" link in my signature, they should have what you need.

b) You don't, you use one of the options I mentioned before (in Access set up linked tables, or do the linking inside your SQL statements).