Optimisation - Query Tables

[Carly]

I have got the following code for querying SQL tables and Access tables depending on a number variable passed to the sub but I was wondering if there was a way of only having the one connection instead of one for SQL & one for Access:

Sub Query(x As Integer)
    
    Dim vSQL As String
    Dim vConnection As String
    Dim i As Integer
    
1:  On Error GoTo ErrHand
    
2:  If x = 1 Then
    
3:      vSQL = "Select customerno, salesoffice, reparea, targetacc from tCustomer"
    
4:      vConnection = _
            "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;" _
            & "Initial Catalog=Consol;Data Source=ReportDB;" _
            & "Use Procedure for Prepare=1;" _
            & "Auto Translate=True;" _
            & "Packet Size=4096;"
            
5:      ActiveWorkbook.Connections.Add "Consol", "SQL Commander Connection", vConnection, vSQL, 2
    
6:      i = ActiveWorkbook.Connections.Count
            
7:      With ActiveSheet.QueryTables.Add(ActiveWorkbook.Connections.Item(i).OLEDBConnection.Connection, ActiveSheet.Range("A1"), vSQL)
            .Name = "Data"
            .FieldNames = True
            .PreserveFormatting = True
            .BackgroundQuery = True
            .RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
        
8:      ActiveWorkbook.Connections.Item(i).Delete

9:  Else

10:     vConnection = "ODBC;DSN=MS Access Database;DBQ=\\Stoke\analytic$\Master lookup tables\Analytics Lookups and Master Files.mdb;" _
            & "DefaultDir=\\Stoke\analytic$\Master lookup tables;" _
            & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
11:     If x = 2 Then
        
12:         vSQL = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `\\Stoke\analytic$\Master lookup tables\Analytics Lookups and Master Files`.`RSD-MASTER` `RSD-MASTER`"

13:     Else
        
14:         vSQL = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `\\Stoke\analytic$\Master lookup tables\Analytics Lookups and Master Files`.`AM TAM Areas` `AM TAM Areas`"
        
        End If
        
15:     With ActiveSheet.QueryTables.Add(Connection:=vConnection, Destination:=Range("A1"))
            .CommandText = vSQL
            .Name = "Query from MS Access Database"
            .FieldNames = True
            .PreserveFormatting = True
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With

    End If
        
16: Exit Sub

ErrHand:

     MsgBox ("An error has occured in the ComArr sub on line " & Erl _
        & vbCrLf & Err.Number & ": Error Description: " & Err.Description _
        & vbCrLf _
        & vbCrLf & "YOU WILL HAVE TO CLOSE DOWN & RESET THE TEMPLATE BEFORE YOU RE-RUN")

End Sub

This code works absolutely perfectly with the help of MikeSel but I was just wondering if it could be optimised.

Any help would be greatly received.