How to make a SQL statement dynamic in a vba macro by referencing a declared variable ?

Database
Fan

How to make a SQL statement dynamic in a vba macro by referencing a declared variable ?

Hi, one routine of my job is to use sql to get the performance data for specific agents. I am writing a vba macro to make this process easier for me. So I want to open an Excel workbook, and entre the required agent ID / agent Pein in cell J9, and once I run the macro, I could get the data I need without openning Teradata and using almost the same query code everytime (only thing changing is the agent ID/Pein). Below is the Macro I've written, but it's not working. Can someone help me please? Thanks.

 

 

Dim AgentPein As String
AgentPein = Cells(9, "J")
'
Workbooks.Add

Dim cnDB As New ADODB.Connection
Dim RS As New ADODB.Recordset, sqlText As String
Dim cmd As New ADODB.Command
sqlText = "Select * FROM SBI_MDU.VMENSA_SALES_COHORT_DLY where pein = 'AgentPein' "

 

cnDB.Open "DSN=**bleep**;UID=**bleep**;Password=**bleep**;"
cmd.ActiveConnection = cnDB
cmd.CommandType = adCmdText
cmd.CommandText = sqlText
RS.Open sqlText, cnDB
Set RS = cmd.Execute
For x = 0 To RS.Fields.Count - 1
Cells(1, x + 1) = RS.Fields(x).Name
Next
Range("A2").CopyFromRecordset RS
RS.Close
Set RS = Nothing
cnDB.Close
Set cnDB = Nothing

2 REPLIES
Highlighted
Teradata Employee

Re: How to make a SQL statement dynamic in a vba macro by referencing a declared variable ?

sqlText = "Select * FROM SBI_MDU.VMENSA_SALES_COHORT_DLY where pein = '"&AgentPein"'"

Fan

Re: How to make a SQL statement dynamic in a vba macro by referencing a declared variable ?

Thanks Fred, but it was not working. Are we missing a "&" in this code?