I am a rookie at this but am having fun learning. I have 2 tables on 2 different servers that I want to join. One is an Oracle Server, one is a SQL server. How can I do this with VBA in Excel? Here is the code to do them independently:

Sub Query1()
Dim varConn As String
Dim varSQL As String

Range("A1").CurrentRegion.ClearContents

varConn = "OLEDB;Provider=SQLOLEDB.1;Password=Password;Persi st Security Info=True;User ID=Password;Data Source=Source;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ID;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Catalog"

varSQL = "SELECT * FROM Table1"

With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
.CommandText = varSQL
.Name = "Table1"
.Refresh BackgroundQuery:=False
End With

End Sub



Sub Query2()
Dim varConn As String
Dim varSQL As String

Range("A1").CurrentRegion.ClearContents

varConn = "OLEDB;Provider=OraOLEDB.Oracle.1;Password=Passwor d;Persist Security Info=True;User ID=ID;Data Source=Source;Extended Properties="""""

varSQL = "SELECT * FROM Table2"

With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
.CommandText = varSQL
.Name = "Table2"
.Refresh BackgroundQuery:=False
End With

End Sub



THANKS!!!

I am NOT a Microsoft person at all.
I can not discern which query is going against which DB.
So which is Oracle table & which is SQL Server table?

You can have the Oracle Table linked in SQL Server and then have an SQL Server connection and run the join query.