Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: How can I use VBA in excel to Join 2 tables from different servers

    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!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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 lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    You can have the Oracle Table linked in SQL Server and then have an SQL Server connection and run the join query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •