Trying setup report which would pull data from Teradata based on query which is already written but somehow i need pass two parameters to query (ie year , week) stored in excel. Did search forum and i can't find anything close to what I'm trying find out.
Would be there any chance that someone can share some examples how ODBC connection setup is looking and how pass parameter to filer the query?
Here example using a single parameter, executing in Excel.
##Excel VB MACRO##
Rem To Retrieve Data from Teradata Database
Sheets("L3 Memory").Cells(1, 5).Select
ODBCnm = Sheets("Config").Cells(2, 3).Value
DateParm = Sheets("Memory").Cells(1, 6).Value
RunMacro = "exec PDCRINFO.RPT_Memory (" & DateParm & ");"
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=" & ODBCnm & ";;;; " _
.CommandText = RunMacro
.Name = "RptMem"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
REPLACE MACRO PDCRINFO.RPT_Memory (RunDate DATE) AS (
LOCKING ROW FOR ACCESS --EXEC PDCRINFO.RPT_Memory (CURRENT_DATE);
SEL TheDate "Date"
,COALESCE(P.DescShort ,'*Error*') AS "Time"
,Max(MaxSwapSec) "Max Swap Sec" ,AVG(AvgSwapSec) "Avg Swap Sec"
,MIN(MinMemFree) "Min Mem Free" ,AVG(AvgMemFree) "Avg Mem Free"
FROM PDCRDATA.Hist_Memory A
LEFT OUTER JOIN PDCRDATA.Tool_periods P
ON A.TheHour BETWEEN P.StartHour AND P.EndHour-1
WHERE TheDate BETWEEN :RunDate-31 AND :RunDate
GROUP BY 1,2,P.PeriodID
ORDER BY TheDate DESC, P.PeriodID DESC;
I did look on your code but for some reason it's not working when addng to my VBA code which is slightly defferent.
Field to which ConcatSQL is reffering holds "John Smith" without quotation marks as value.
Dim ConcatSQL As String ConcatSQL = Sheets("LogIn").Cells(8, 3).Value QueryA = "select * from database.table WHERE Name = " & ConcatSQL & "
cmdSQLData.CommandText = QueryA
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0
Set rs = cmdSQLData.Execute()
Row = 1
rs.MoveFirst ' it's falling here
Do While (rs.EOF = False And rs.BOF = False)
p = rs.GetRows(1)
When running this i can see that SQL string is exactly as in SQL Assistant however I'm getting bellow error message.
What i'm actually doing wrong as i did spend last hour without any joy.