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?
Thank you in advanced for any help.
Jon
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?
Code:
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