VBA, ADO.Connection and query parameters

Question

I have excel VBA script:

Set cоnn = CreateObject("ADODB.Connection")
conn.Open "report"
Set rs = conn.Execute("select * from table" ) 

Script work fine, but i want to add parameter to it. For example " where (parentid = myparam)", where myparam setted outside query string. How can i do it?

Of course i can modify query string, but i think it not very wise.

Answer 1

You need to use an ADODB.Command object that you can add parameters to. Here's basically what that looks like

Sub adotest()

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.Parameter
    Dim Rs as ADODB.Recordset

    Set Cn = New ADODB.Connection
    Cn.Open "mystring"
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = "SELECT * FROM table WHERE parentid=?;"
        .CommandType = adCmdText

        Set Pm = .CreateParameter("parentid", adNumeric, adParamInput)
        Pm.Value = 1

        .Parameters.Append Pm

        Set Rs = .Execute
    End With

End Sub

The question mark in the CommandText is the placeholder for the parameter. I believe, but I'm not positive, that the order you Append parameters must match the order of the questions marks (when you have more than one). Don't be fooled that the parameter is named "parentid" because I don't think ADO cares about the name other than for identification.