join tables from different databases...

[Tom]

Hi,

I know how to write a stored procedure in TSQL for SQLServer to join tables
from different databases but how do I accomplish this in MS Access? (My
enviroment: ADO and VC++6.0, Office2000, Win2000).

Thanks in advance!
/Tom

 

[Arvin Meyer]

The easiest way will be to link the tables from the external database to the
local database and simply join the tables. But if you don't want to link the
table from the outside database, here's some SQL to show you how to connect
a table from an external database to a table in an internal database and
join the 2 tables:

SELECT Table1.PersonID, Table2.PersonID, *
FROM [C:\Data.mdb].Table1 INNER JOIN Table2 ON Table1.PersonID =
Table2.PersonID;

Table one is in an external database: C:\Data.mdb

Just observe the connection structure (including the square brackets) for
each external table if you have multiple external databases:

[C:\Path to database.mdb].TableName
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tom]

The easiest way will be to link the tables from the external database to the
local database and simply join the tables. But if you don't want to link the
table from the outside database, here's some SQL to show you how to connect
a table from an external database to a table in an internal database and
join the 2 tables:

SELECT Table1.PersonID, Table2.PersonID, *
FROM [C:\Data.mdb].Table1 INNER JOIN Table2 ON Table1.PersonID =
Table2.PersonID;

Table one is in an external database: C:\Data.mdb

Just observe the connection structure (including the square brackets) for
each external table if you have multiple external databases:

[C:\Path to database.mdb].TableName

Wow, is it that easy!?! I asked my local vb/access-guru and he went on
to first say it wasn't possible and then began to talk about something he
called linked tables and vba and...

I must try your suggestion right now - thanks!!

/Tom