[AccessD] How to parametrize an IN ( ) clause with ADO

[Gustav Brock]

Hi Bill

Nope. That is wishful thinking only. Could be nice, also to include a function to returns the INs.

You'll have to create the full (literal) IN clause dynamically.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Bill Benson
Sendt: 28. februar 2018 14:42
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] How to parametrize an IN ( ) clause with ADO

I noticed this message I never posted in my Drafts folder. I don't think I ever resolved it, just abandoned it. Is there a way to create a parameter query that avails of an IN operator in the WHERE clause? The attempt below does not work as written.

I think I do not need Set Rst = CreateObject("ADODB.Recordset") but I added it just for good measure.


Dim Cmd As Object
Dim Rst As Object
Dim paramFileType As ADODB.Parameter

Set Cmd = CreateObject("ADODB.Command")
Set Rst = CreateObject("ADODB.Recordset") With Cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = 1 'adCmdText=1
    .CommandText = "Select Description From [_Files] Where Description IN ([pDescription])"
    Set paramFileType = .CreateParameter("[pDescription]", 200, 1, 255,
"'Template','Output','Manual'") 'adVarChar = 200, adParamInput = 1
    .Parameters.Append paramFileType

    Set Rst = .Execute()
End With