ODBC DSN connection

[123DavidRhodes]

Hi All,

The following code was generated with record macro. It works great in the workbook it was created from but fails on other workbooks or if I delete the worksheet that I started the record from it fails. In other words let's say it was Sheet1 that I started the record. If I add Sheet2 it works on Sheet2. But if I delete Sheet1 the macro no longer works. I get subscript out of range from the first With method (see comment in code below). I know why. It's because the ODBC DSN is no longer in reference but darn it I don't know how to solve that problem. Anyone have any suggestions?

Sub Macro67()
'
' Macro67 Macro
'

Dim ws
Dim wks As Worksheet
Dim wks2 As Worksheet

Set wks = Sheets("Sheet7")
Set wks2 = Sheets(ActiveSheet.Name)
ws = ActiveWorkbook.Name

'********  I get an error "Subscript out of range" with the next line **********
'********  And for the life of me I cannot figure out why ********************

    With Workbooks(ws).Connections("ABC_BI_PRD1_MASTER ROUTE_CUSTOMER"). _
        ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT * FROM ABC_BI_PRD1_MASTER.DBO.ROUTE_CUSTOMER Where CUSTOMER_UID = " & wks.Range("A2").Value & ";")
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=MasterRoute;Database=ABC_BI_PRD1_MASTER;Servername=10.10.10.10;UID=MyUserName;SchemaName=;Port=5480;ReadOnly=0;SQLBitOneZero=" _
        ), Array( _
        "0;FastSelect=0;ForceCacheUsername=0;LegacySQLTables=0;NumericAsChar=0;ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateForm" _
        ), Array("at=1;SecurityLevel=preferredUnSecured;CaCertFile="))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("ABC_BI_PRD1_MASTER ROUTE_CUSTOMER")
        .Name = "ABC_BI_PRD1_MASTER ROUTE_CUSTOMER"
        .Description = ""
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=MasterRoute;Database=ABC_BI_PRD1_MASTER;Servername=10.10.10.10;UID=MyUserName;SchemaName=;Port=5480;ReadOnly=0;SQLBitOneZero=" _
        ), Array( _
        "0;FastSelect=0;ForceCacheUsername=0;LegacySQLTables=0;NumericAsChar=0;ShowSystemTables=0;LoginTimeout=0;QueryTimeout=0;DateForm" _
        ), Array("at=1;SecurityLevel=preferredUnSecured;CaCertFile=")), Destination:= _
        wks2.Range("$A$1")).QueryTable
        '.CommandType = 0
        .CommandText = Array( _
        "SELECT * FROM ABC_BI_PRD1_MASTER.DBO.ROUTE_CUSTOMER Where CUSTOMER_UID = " & wks.Range("A2").Value)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.ListObject.DisplayName = "Table_ABC_BI_PRD1_MASTER_ROUTE_CUSTOMER2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[123DavidRhodes]

OK I solved the problem and I am posting the solution for posterity. I deleted the first two With statements and kept the last one and it all works well now.