Here's the Dynamic SQL, ADODB way to do it (make sure and add the ADODB reference). This was built for string values in the cells. Also there's no error-catching code, please add that as needed:
Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"
strSQL = "Select col4 from Table_1 where col1='" & p1.Value & _
"' and col2='" & p2.Value & _
"' and col3='" & p3.Value & "'"
rst.Open strSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText
Test = rst("col4")
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Function
Here's the ADODB, Command-Parameter way, a little trickier. This one is just setup up for single chars:
Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ccmd As New ADODB.Command
Dim PA1 As New ADODB.Parameter, PA2 As New ADODB.Parameter
Dim PA3 As New ADODB.Parameter
cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"
ccmd.ActiveConnection = cnt
ccmd.CommandText = _
"SELECT col4 FROM Table_1 WHERE col1=? AND col2=? AND col3=?"
ccmd.CommandType = adCmdText
Set PA1 = ccmd.CreateParameter("first", adChar, adParamInput, 1, p1.Value)
Set PA2 = ccmd.CreateParameter("second", adChar, adParamInput, 1, p2.Value)
Set PA3 = ccmd.CreateParameter("third", adChar, adParamInput, 1, p3.Value)
ccmd.Parameters.Append PA1
ccmd.Parameters.Append PA2
ccmd.Parameters.Append PA3
Set rst = ccmd.Execute
Test = rst("col4")
rst.Close
Set rst = Nothing
cnt.Close
Set cnt = Nothing
End Function
Best Answer
So I finally figured it out. Here are my steps.
There are some issues with adding parameters. They only work in the final where clause. i.e. if you are using subqueries, pivots, cte's etc you can't add parameters in those. Only at the end in the final where clause
Click "Ok" twice - a pop up will appear asking you for the parameter
Fill anything in - we'll change this later
Click anywhere in the results and click "Properties" on the Data tab on the ribbon
Click on the little hand property icon
Click on the Definition Tab
You will now see the "Parameters..." button is clickable. Click it and change the option from prompt for value to Get value from the following cell
If anyone finds a quicker, simpler way - I'm all ears, but this works consistently and with really big complicated queries. So for us, it is the right solution