Joining tables from different Databases.

[Guest]

Hello

I have been wrestling with a problem for a few days now and just can't figure it out. I am using Access 2002 Project connected to a Remote SQL Server. I have another database that is a regular .mdb

Problem
How do I join a table from an .mdb database and a SQL database to produce a recordset? I can access the individual tables using connection string and recordset, but if I wanted to use some kind of INNER JOIN statement on two tables that are in different databases and bind the single SQL string to a recordset, I come to a blank. I don't want to create temporary tables and such. Just join two tables that are in different databases in a single sql statement

Thanks
Ty

 

[Joe Fallon]

1. No idea if you can use something like this in an .adp but...
From Help:
"In the next example, the source of data for the query is a dBASE IV table
named Customer in the C:\Dbdata folder. You can enter this SQL statement in
SQL view of a Query window.
SELECT Customer.COMPANYNAM, Customer.PHONE
FROM Customer IN 'c:\dbdata'[dBASE IV;];

2. In SQL Server you can define a Linked Server against JET .mdb files.
Then SQL Server thinks the Jet data is "just another table" so you can do
the join on the server.
I think you use 3 part names for the linked table data.
--
Joe Fallon
Access MVP

Hello,

I have been wrestling with a problem for a few days now and just can't

figure it out. I am using Access 2002 Project connected to a Remote SQL
Server. I have another database that is a regular .mdb.

Problem:
How do I join a table from an .mdb database and a SQL database to produce

a recordset? I can access the individual tables using connection string and
recordset, but if I wanted to use some kind of INNER JOIN statement on two
tables that are in different databases and bind the single SQL string to a
recordset, I come to a blank. I don't want to create temporary tables and
such. Just join two tables that are in different databases in a single sql
statement.

 

[Guest]

Thanks Joe. Although I have come across the help article you mentioned, I still can't do a join and do it in code. I can't believe with all the ODBC, DAO, ADO stuff to connect to different databases that there isn't a simple documentation out there that would show you how to join two tables from different databases to create a single query. I will keep searching whether it is possible or not


----- Joe Fallon wrote: ----

1. No idea if you can use something like this in an .adp but..
From Help
"In the next example, the source of data for the query is a dBASE IV tabl
named Customer in the C:\Dbdata folder. You can enter this SQL statement i
SQL view of a Query window
SELECT Customer.COMPANYNAM, Customer.PHON
FROM Customer IN 'c:\dbdata'[dBASE IV;]

2. In SQL Server you can define a Linked Server against JET .mdb files
Then SQL Server thinks the Jet data is "just another table" so you can d
the join on the server
I think you use 3 part names for the linked table data
--
Joe Fallo
Access MV

Hello

figure it out. I am using Access 2002 Project connected to a Remote SQ
Server. I have another database that is a regular .mdb

How do I join a table from an .mdb database and a SQL database to produc

a recordset? I can access the individual tables using connection string an
recordset, but if I wanted to use some kind of INNER JOIN statement on tw
tables that are in different databases and bind the single SQL string to
recordset, I come to a blank. I don't want to create temporary tables an
such. Just join two tables that are in different databases in a single sq
statement

 

[Joe Fallon]

#2 does allow it.
Just set up the linked server.
--
Joe Fallon
Access MVP

Thanks Joe. Although I have come across the help article you mentioned, I

still can't do a join and do it in code. I can't believe with all the ODBC,
DAO, ADO stuff to connect to different databases that there isn't a simple
documentation out there that would show you how to join two tables from
different databases to create a single query. I will keep searching whether
it is possible or not.

----- Joe Fallon wrote: -----

1. No idea if you can use something like this in an .adp but...
From Help:
"In the next example, the source of data for the query is a dBASE IV table
named Customer in the C:\Dbdata folder. You can enter this SQL statement in
SQL view of a Query window.
SELECT Customer.COMPANYNAM, Customer.PHONE
FROM Customer IN 'c:\dbdata'[dBASE IV;];

2. In SQL Server you can define a Linked Server against JET .mdb files.
Then SQL Server thinks the Jet data is "just another table" so you can do
the join on the server.
I think you use 3 part names for the linked table data.
--
Joe Fallon
Access MVP

Hello,

can't
figure it out. I am using Access 2002 Project connected to a Remote SQL
Server. I have another database that is a regular .mdb.

How do I join a table from an .mdb database and a SQL database to

produce
a recordset? I can access the individual tables using connection string and
recordset, but if I wanted to use some kind of INNER JOIN statement on two
tables that are in different databases and bind the single SQL string to a
recordset, I come to a blank. I don't want to create temporary tables and
such. Just join two tables that are in different databases in a single sql
statement.

Ty