Please help with SQL Query Question using EXCEL & VBA

[Dan]

Hi, I was wondering if it was possible to join tables from two seperate
databases? I need to join the tables from on database serve, say "Server1"
and another from "Server2". I don't know if this is possible as i have
always used the sql command when opening a single database. Sorry, i'm
confusing myself even.

I was hoping something like this was possible...

SQL = "Select server1.dbo.table1.item1 ,server2.dbo.table1.item2 from
server1.dbo.table1 INNER JOIN server2.dbo.table1 where
server1.dbo.table1.item1 = server2.dbo.table1.item1"

Is there a way of doing an operation that would get me the results i'm
looking for with EXCEL in VBA?

Any help would be great!

Dan Butler
(e-mail address removed)

 

[Jake Marx]

Hi Dan,

It's unclear whether you're asking about 2 databases _on the same server_ or
2 distinct servers running SQL Server. If you are talking about 2 different
databases on the same server, you can do this fairly easily:

SELECT t1.ID, t2.Name
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2 ON t1.ID=t2.ID

If you are talking about 2 separate servers, then it's a similar construct:

SELECT t1.ID, t2.Name
FROM server1.database1.dbo.table1 t1
INNER JOIN server2.database2.dbo.table2 t2 ON t1.ID=t2.ID

However, in the second case, you must make sure server2 is set up as a
"Linked Server" on server1. You can do this through the Security node in
Enterprise Manager.

From VBA, your ADO connection would be the same as if you were connecting
only to server1. The Linked Server connection from server1 to server2 will
take care of it from there.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Dan]

Thanks for the resonse Jake. I'm dealing with 2 distinct servers. I'm not
sure if they are linked as they are at work and I have no control over SQL
enterprise there. I am going to hope they are since i'm sure they must be
and try your suggestion next time i step in the office. I've emulated works
DATABASE at home with MSSQL Server and it works. Thanks alot!

Dan Butler
(e-mail address removed)

Hi Dan,

It's unclear whether you're asking about 2 databases _on the same server_ or
2 distinct servers running SQL Server. If you are talking about 2 different
databases on the same server, you can do this fairly easily:

SELECT t1.ID, t2.Name
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2 ON t1.ID=t2.ID

If you are talking about 2 separate servers, then it's a similar construct:

SELECT t1.ID, t2.Name
FROM server1.database1.dbo.table1 t1
INNER JOIN server2.database2.dbo.table2 t2 ON t1.ID=t2.ID

However, in the second case, you must make sure server2 is set up as a
"Linked Server" on server1. You can do this through the Security node in
Enterprise Manager.

From VBA, your ADO connection would be the same as if you were connecting
only to server1. The Linked Server connection from server1 to server2 will
take care of it from there.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, I was wondering if it was possible to join tables from two
seperate databases? I need to join the tables from on database serve,
say "Server1" and another from "Server2". I don't know if this is
possible as i have always used the sql command when opening a single
database. Sorry, i'm confusing myself even.

I was hoping something like this was possible...

SQL = "Select server1.dbo.table1.item1 ,server2.dbo.table1.item2 from
server1.dbo.table1 INNER JOIN server2.dbo.table1 where
server1.dbo.table1.item1 = server2.dbo.table1.item1"

Is there a way of doing an operation that would get me the results i'm
looking for with EXCEL in VBA?

Any help would be great!

Dan Butler
(e-mail address removed)