Condensing code
 

Two questions
The below works is everything needed?
What does the last "with" statement really do??

Sub GetValues()

Dim strDate As String
strDate = Format(shtInput.Range("flyerdate"), "yyyy-mm-dd")

With ActiveWorkbook.Connections("Query from DB2P").ODBCConnection
.BackgroundQuery = False
.CommandText = "SELECT TAYVPHIS_0.PERF_AS_OF_DT, " & _
"TAYVPHIS_0.PROD_NUM, " & _
"TAYVPHIS_0.INV_MED_CD, " & _
"TAYVPHIS_0.SUR_CHRG_IND, " & _
"TAYVPHIS_0.PERF_SINCE_INCEP, " & _
"TAYVPHIS_0.PERF_SINCE_INCLU, " & _
"TAYVPHIS_0.PERF_10_YR, " & _
"TAYVPHIS_0.PERF_5_YR, " & _
"TAYVPHIS_0.PERF_1_YR, " & _
"TAYVPHIS_0.PERF_3_MO" & Chr(13) & "" & Chr(10) & _
"FROM PRDDB2.TAYVPHIS TAYVPHIS_0" & Chr(13) & "" & Chr(10) & _
"WHERE (TAYVPHIS_0.PERF_AS_OF_DT={d '" & strDate & "' })"
.CommandType = xlCmdSql
.Connection = _

"ODBC;DSN=XXXX;UID=XXXXXX;IpAddress=XXXXXX;TcpPort =446;Location=xxxxxx;"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from DB2P")
.Name = "Query from DB2P"
.Description = ""
End With
ActiveWorkbook.Connections("Query from DB2P").Refresh
End Sub

Condensing code
 

I haven't looked at the material for your first question, but to quickly
answer your second question... the With statement is providing the object
that the .Name and .Description (notice the "dots" in front of them) refers
back to (which is the main purpose of the With statement... it relieves you
of having to continually type the object over and over again for each
property or method referencing that object).

--
Rick (MVP - Excel)


"Brad" wrote in message
...
Two questions
The below works is everything needed?
What does the last "with" statement really do??

Sub GetValues()

Dim strDate As String
strDate = Format(shtInput.Range("flyerdate"), "yyyy-mm-dd")

With ActiveWorkbook.Connections("Query from DB2P").ODBCConnection
.BackgroundQuery = False
.CommandText = "SELECT TAYVPHIS_0.PERF_AS_OF_DT, " & _
"TAYVPHIS_0.PROD_NUM, " & _
"TAYVPHIS_0.INV_MED_CD, " & _
"TAYVPHIS_0.SUR_CHRG_IND, " & _
"TAYVPHIS_0.PERF_SINCE_INCEP, " & _
"TAYVPHIS_0.PERF_SINCE_INCLU, " & _
"TAYVPHIS_0.PERF_10_YR, " & _
"TAYVPHIS_0.PERF_5_YR, " & _
"TAYVPHIS_0.PERF_1_YR, " & _
"TAYVPHIS_0.PERF_3_MO" & Chr(13) & "" & Chr(10) & _
"FROM PRDDB2.TAYVPHIS TAYVPHIS_0" & Chr(13) & "" & Chr(10) & _
"WHERE (TAYVPHIS_0.PERF_AS_OF_DT={d '" & strDate & "' })"
.CommandType = xlCmdSql
.Connection = _

"ODBC;DSN=XXXX;UID=XXXXXX;IpAddress=XXXXXX;TcpPort =446;Location=xxxxxx;"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Query from DB2P")
.Name = "Query from DB2P"
.Description = ""
End With
ActiveWorkbook.Connections("Query from DB2P").Refresh
End Sub