Select Query join from 2 different databases?

  • Thread starter Thread starter Özden Irmak
  • Start date Start date
Ö

Özden Irmak

Hello,

I want to create a Select join query from 2 different databases? How can I
do this?

Regards,

Özden
 
Özden Irmak said:
Hello,

I want to create a Select join query from 2 different databases? How can I
do this?
Click to expand...

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON tblPeople.PersonID =
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hello Again,

I want to achieve this without linking so I was looking for the proper sql
structure for this...

As an additional and important question, what if the 2nd joined database has
a password or even associated with a workgroup file?

Regards,

Özden

Arvin Meyer said:
Özden Irmak said:
Hello,

I want to create a Select join query from 2 different databases? How can I
do this?
Click to expand...

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON tblPeople.PersonID =
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
 
You will need to have the second joined database opened with a proper
connection string. The only way I've ever done this is in code, not in a
saved query, and the code only referenced one database, not 2. I'm not sure
that this is possible within Access. The code I supplied before is very
esoteric and not generally supported. The way I'd attempt it is to create
the query in the secure database, then open a connection in code from your
database and get the results in a recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Özden Irmak said:
Hello Again,

I want to achieve this without linking so I was looking for the proper sql
structure for this...

As an additional and important question, what if the 2nd joined database has
a password or even associated with a workgroup file?

Regards,

Özden

can
I
do this?
Click to expand...

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON
Click to expand...
tblPeople.PersonID
=
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
 
Hello Arvin,

Thanks for your both replies by the way...

I can open the second database with the proper connection string but I
didn't understand how to merge them to make a single recordset returned by
the query?

Can you give me more clear info or even your method you used before as an
example?

I'm not an Access user primarily (Using VB and C#) so I don't look for a
wizard or something like that which can be achieved by inside Access, a code
solution is ok for me...

Thanks again,

Özden

Arvin Meyer said:
You will need to have the second joined database opened with a proper
connection string. The only way I've ever done this is in code, not in a
saved query, and the code only referenced one database, not 2. I'm not sure
that this is possible within Access. The code I supplied before is very
esoteric and not generally supported. The way I'd attempt it is to create
the query in the secure database, then open a connection in code from your
database and get the results in a recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Özden Irmak said:
Hello Again,

I want to achieve this without linking so I was looking for the proper sql
structure for this...

As an additional and important question, what if the 2nd joined database has
a password or even associated with a workgroup file?

Regards,

Özden

Arvin Meyer said:
Hello,

I want to create a Select join query from 2 different databases? How
Click to expand...
can
I
do this?

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON
Click to expand...
tblPeople.PersonID
=
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
Click to expand...
 
This might help:

SELECT tblAddresses.PersonID, tblAddresses.City, tblPeople.FirstName,
tblPeople.LastName
FROM [F:\Data.mdb].tblAddresses INNER JOIN tblPeople ON
tblAddresses.PersonID = tblPeople.PersonID;

tblAddresses is a table on the remote database on another server that, is
mapped to the F: Drive There is no link, just the path to the database and
the table which by itself looks like:

[F:\Data.mdb].tblAddresses

tblPeople is a local table in the database we are running the query from.
You can also run separate queries in Access (not in VB because you need the
Access Expression Service) Like this:

SELECT tblPeople.PersonID, tblPeople.FirstName, tblPeople.LastName
FROM [Z:\Data2.mdb].tblPeople;

and save it as Query1, then:

SELECT tblAddresses.PersonID, tblAddresses.City
[F:\Data1.mdb].tblAddresses

and save it as Query2, then:

SELECT Query1.PersonID, Query1.City, Query2.FirstName, Query2.LastName
FROM Query1 INNER JOIN Query2 ON Query1.PersonID = Query2.PersonID;

The above is joining 2 queries run on 2 separate tables from 2 separate
unlinked databases running on 2 different servers which are mapped to drive
letters on a third machine. AFAIK, only Access can do something like that!
BTW, this was tested using 3 machines on my network running databases tables
with 20K rows in the address table and 5100 rows in the people table and it
returned the matched records in about 1.5 or 2 seconds.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Özden Irmak said:
Hello Arvin,

Thanks for your both replies by the way...

I can open the second database with the proper connection string but I
didn't understand how to merge them to make a single recordset returned by
the query?

Can you give me more clear info or even your method you used before as an
example?

I'm not an Access user primarily (Using VB and C#) so I don't look for a
wizard or something like that which can be achieved by inside Access, a code
solution is ok for me...

Thanks again,

Özden

Arvin Meyer said:
You will need to have the second joined database opened with a proper
connection string. The only way I've ever done this is in code, not in a
saved query, and the code only referenced one database, not 2. I'm not sure
that this is possible within Access. The code I supplied before is very
esoteric and not generally supported. The way I'd attempt it is to create
the query in the secure database, then open a connection in code from your
database and get the results in a recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Özden Irmak said:
Hello Again,

I want to achieve this without linking so I was looking for the proper sql
structure for this...

As an additional and important question, what if the 2nd joined
Click to expand...
database
has
a password or even associated with a workgroup file?

Regards,

Özden

Hello,

I want to create a Select join query from 2 different databases?
Click to expand...
How
can
I
do this?

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON tblPeople.PersonID
=
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
Click to expand...
 
Hello Arvin,

Thanks again for your valuable info...

I did understand those two methods on how to merge two tables from different
databases...However,now, my point is different...

When you collect the data from the external database you only give the path
of it and according to the Jet 4.0 Sql Reference a second thing you can
mention is it's type (eg. an excell spreadsheet, a dbase table,etc.). My
problem is that when the databases are password enabled or worser has
workgroup information attached to them, these two methods doesn't work.

I think my only solution is to somehow link the external tables from code
and drop them at the end? Do you know any solution for this?

Best Regards,

Özden

Arvin Meyer said:
This might help:

SELECT tblAddresses.PersonID, tblAddresses.City, tblPeople.FirstName,
tblPeople.LastName
FROM [F:\Data.mdb].tblAddresses INNER JOIN tblPeople ON
tblAddresses.PersonID = tblPeople.PersonID;

tblAddresses is a table on the remote database on another server that, is
mapped to the F: Drive There is no link, just the path to the database and
the table which by itself looks like:

[F:\Data.mdb].tblAddresses

tblPeople is a local table in the database we are running the query from.
You can also run separate queries in Access (not in VB because you need the
Access Expression Service) Like this:

SELECT tblPeople.PersonID, tblPeople.FirstName, tblPeople.LastName
FROM [Z:\Data2.mdb].tblPeople;

and save it as Query1, then:

SELECT tblAddresses.PersonID, tblAddresses.City
[F:\Data1.mdb].tblAddresses

and save it as Query2, then:

SELECT Query1.PersonID, Query1.City, Query2.FirstName, Query2.LastName
FROM Query1 INNER JOIN Query2 ON Query1.PersonID = Query2.PersonID;

The above is joining 2 queries run on 2 separate tables from 2 separate
unlinked databases running on 2 different servers which are mapped to drive
letters on a third machine. AFAIK, only Access can do something like that!
BTW, this was tested using 3 machines on my network running databases tables
with 20K rows in the address table and 5100 rows in the people table and it
returned the matched records in about 1.5 or 2 seconds.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Özden Irmak said:
Hello Arvin,

Thanks for your both replies by the way...

I can open the second database with the proper connection string but I
didn't understand how to merge them to make a single recordset returned by
the query?

Can you give me more clear info or even your method you used before as an
example?

I'm not an Access user primarily (Using VB and C#) so I don't look for a
wizard or something like that which can be achieved by inside Access, a code
solution is ok for me...

Thanks again,

Özden

Arvin Meyer said:
You will need to have the second joined database opened with a proper
connection string. The only way I've ever done this is in code, not in a
saved query, and the code only referenced one database, not 2. I'm not sure
that this is possible within Access. The code I supplied before is very
esoteric and not generally supported. The way I'd attempt it is to create
the query in the secure database, then open a connection in code from your
database and get the results in a recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hello Again,

I want to achieve this without linking so I was looking for the
Click to expand...
proper
sql
structure for this...

As an additional and important question, what if the 2nd joined database
has
a password or even associated with a workgroup file?

Regards,

Özden

Hello,

I want to create a Select join query from 2 different databases? How
can
I
do this?

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON
tblPeople.PersonID
=
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
Click to expand...
 
I think I found my answer...

.Properties("Jet OLEDB:Create Link")
.Properties("Jet OLEDB:Link Datasource")
.Properties("Jet OLEDB:Remote Table Name")

Thanks for all your help...

Regards,

Özden


Özden Irmak said:
Hello Arvin,

Thanks again for your valuable info...

I did understand those two methods on how to merge two tables from different
databases...However,now, my point is different...

When you collect the data from the external database you only give the path
of it and according to the Jet 4.0 Sql Reference a second thing you can
mention is it's type (eg. an excell spreadsheet, a dbase table,etc.). My
problem is that when the databases are password enabled or worser has
workgroup information attached to them, these two methods doesn't work.

I think my only solution is to somehow link the external tables from code
and drop them at the end? Do you know any solution for this?

Best Regards,

Özden

Arvin Meyer said:
This might help:

SELECT tblAddresses.PersonID, tblAddresses.City, tblPeople.FirstName,
tblPeople.LastName
FROM [F:\Data.mdb].tblAddresses INNER JOIN tblPeople ON
tblAddresses.PersonID = tblPeople.PersonID;

tblAddresses is a table on the remote database on another server that, is
mapped to the F: Drive There is no link, just the path to the database and
the table which by itself looks like:

[F:\Data.mdb].tblAddresses

tblPeople is a local table in the database we are running the query from.
You can also run separate queries in Access (not in VB because you need the
Access Expression Service) Like this:

SELECT tblPeople.PersonID, tblPeople.FirstName, tblPeople.LastName
FROM [Z:\Data2.mdb].tblPeople;

and save it as Query1, then:

SELECT tblAddresses.PersonID, tblAddresses.City
[F:\Data1.mdb].tblAddresses

and save it as Query2, then:

SELECT Query1.PersonID, Query1.City, Query2.FirstName, Query2.LastName
FROM Query1 INNER JOIN Query2 ON Query1.PersonID = Query2.PersonID;

The above is joining 2 queries run on 2 separate tables from 2 separate
unlinked databases running on 2 different servers which are mapped to drive
letters on a third machine. AFAIK, only Access can do something like that!
BTW, this was tested using 3 machines on my network running databases tables
with 20K rows in the address table and 5100 rows in the people table and it
returned the matched records in about 1.5 or 2 seconds.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access


Özden Irmak said:
Hello Arvin,

Thanks for your both replies by the way...

I can open the second database with the proper connection string but I
didn't understand how to merge them to make a single recordset
Click to expand...
Click to expand...
returned
by a
code
Click to expand...
in
a
saved query, and the code only referenced one database, not 2. I'm not
sure
that this is possible within Access. The code I supplied before is very
esoteric and not generally supported. The way I'd attempt it is to create
the query in the secure database, then open a connection in code
Click to expand...
from
your
database and get the results in a recordset.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hello Again,

I want to achieve this without linking so I was looking for the proper
sql
structure for this...

As an additional and important question, what if the 2nd joined database
has
a password or even associated with a workgroup file?

Regards,

Özden

Hello,

I want to create a Select join query from 2 different
Click to expand...
databases?
How
can
I
do this?

Link the table from the second database to the first database. Use

File ... Get External Data ... Link

to do this. You can also call the other table in SQL code:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON
tblPeople.PersonID
=
tblAddresses.PersonID;

Notice the path to the remote database is in square brackets.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Click to expand...
Click to expand...
Click to expand...
 
Back
Top