[JEQ214]

Hi everyone,

I have a command button that refreshes multiple queries in a worksheet based on what checkboxes a user selects. Each checkbox is a different criteria that will be inserted in the SQL.

My problem is I'm deleting the queries and replacing them. A query called "Top15Office" will now be changed to "Top15Office_1". How do I refresh the query with a different SQL and keep the same query and connection name?

 
Private Sub cmdReQuery_Click()
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim Criteria As String
    Dim sConn As String
    Dim sSQL As String
    Dim oQt As QueryTable
    Dim sh As Worksheet
    Dim CritCnt As Integer
 
    CritCnt = 0
 
    If Me.chkCorpLend.Value = False And Me.chkGBB.Value = False And Me.chkRELend.Value = False And _
    Me.chkRetail.Value = False And Me.chkSAG.Value = False Then
        MsgBox "A group has not been selected"
    Else:
        If Me.chkCorpLend.Value = True Then
            If CritCnt < 1 Then
                Criteria = "'Corporate Lending'"
                CritCnt = CritCnt + 1
            Else
                Criteria = Criteria & ", " & "'Corporate Lending'"
                CritCnt = CritCnt + 1
            End If
        End If
        If Me.chkGBB.Value = True Then
            If CritCnt < 1 Then
                CritCnt = CritCnt + 1
                Criteria = "'GBB'"
            Else
                Criteria = Criteria & ", " & "'GBB'"
                CritCnt = CritCnt + 1
            End If
        End If
        If Me.chkRELend.Value = True Then
            If CritCnt < 1 Then
                CritCnt = CritCnt + 1
                Criteria = "'Real Estate Lending'"
            Else
                Criteria = Criteria & ", " & "'Real Estate Lending'"
                CritCnt = CritCnt + 1
            End If
        End If
        If Me.chkRetail.Value = True Then
            If CritCnt < 1 Then
                CritCnt = CritCnt + 1
                Criteria = "'Retail Commercial / Consumer Lending'"
            Else
                Criteria = Criteria & ", " & "'Retail Commercial / Consumer Lending'"
                CritCnt = CritCnt + 1
            End If
        End If
        If Me.chkSAG.Value = True Then
            If CritCnt < 1 Then
                CritCnt = CritCnt + 1
                Criteria = "'Special Asset Group'"
            Else
                Criteria = Criteria & ", " & "'Special Asset Group'"
                CritCnt = CritCnt + 1
            End If
        End If
    End If
 
    If Me.chkCorpLend.Value = True Or Me.chkGBB.Value = True Or Me.chkRELend.Value = True Or _
    Me.chkRetail.Value = True Or Me.chkSAG.Value = True Then
 
        sConn = "DSN=MS Access Database;DBQ=\\nv-mc044100-dmr\4100\CRE.mdb;DefaultDir=\\nv-mc044100-dmr\4100\;"
        sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
 
        'Office
        Set oCn = New ADODB.Connection
        oCn.ConnectionString = sConn
        oCn.Open
 
        sSQL = "SELECT Top 15 qryComl_Conc_Top15_Office.NAME, qryComl_Conc_Top15_Office.`ACCT#`, " & _
        "qryComl_Conc_Top15_Office.`BANK COMMIT`, qryComl_Conc_Top15_Office.PD_LGD_LEG " & _
        "FROM `\\nv-mc044100-dmr\4100\CRE.mdb`.qryComl_Conc_Top15_Office qryComl_Conc_Top15_Office " & _
        "WHERE (qryComl_Conc_Top15_Office.BUS_LINE In (" & Criteria & ")) " & _
        "ORDER BY qryComl_Conc_Top15_Office.`BANK COMMIT` DESC"
 
        Set oRS = New ADODB.Recordset
        oRS.Source = sSQL
        oRS.ActiveConnection = oCn
        oRS.Open
 
        ActiveWorkbook.Connections("Top15Office").Delete
        Sheet25.QueryTables("Top15Office").Delete
        Range("A8:D23").ClearContents
        Set oQt = Worksheets(6).QueryTables.Add(Connection:=oRS, Destination:=Range("A8"))
            oQt.Name = "Top15Office"
            oQt.PreserveFormatting = True
            oQt.RefreshOnFileOpen = False
            oQt.RefreshStyle = xlOverwriteCells
            oQt.AdjustColumnWidth = False
            oQt.Refresh
 
        ActiveWorkbook.Connections("Connection").Name = "Top15Office"
 
 
        'Gaming
        Set oCn = New ADODB.Connection
        oCn.ConnectionString = sConn
        oCn.Open
        sSQL = "SELECT Top 15 qryComl_Conc_Top15_Gaming.NAME, qryComl_Conc_Top15_Gaming.`ACCT#`, " & _
        "qryComl_Conc_Top15_Gaming.`BANK COMMIT AMT`, qryComl_Conc_Top15_Gaming.PD_LGD_LEG " & _
        "FROM `\\nv-mc044100-dmr\4100\CRE.mdb`.qryComl_Conc_Top15_Gaming qryComl_Conc_Top15_Gaming " & _
        "WHERE (qryComl_Conc_Top15_Gaming.BUS_LINE In (" & Criteria & ")) " & _
        "ORDER BY qryComl_Conc_Top15_Gaming.`BANK COMMIT AMT` DESC"
        Set oRS = New ADODB.Recordset
        oRS.Source = sSQL
        oRS.ActiveConnection = oCn
        oRS.Open
        ActiveWorkbook.Connections("Top15Gaming").Delete
        Sheet25.QueryTables("Top15Gaming").Delete
        Range("G8:J23").ClearContents
        Set oQt = Worksheets(6).QueryTables.Add(Connection:=oRS, Destination:=Range("G8"))
            oQt.Name = "Top15Gaming"
            oQt.PreserveFormatting = True
            oQt.RefreshOnFileOpen = False
            oQt.RefreshStyle = xlOverwriteCells
            oQt.AdjustColumnWidth = False
            oQt.Refresh
        ActiveWorkbook.Connections("Connection").Name = "Top15Gaming"
 
        If Not oRS Is Nothing Then Set oRS = Nothing
        If Not oCn Is Nothing Then Set oCn = Nothing
    End If
    CritCnt = 0
End Sub

Thank you in advanced for any help.

Jon