ADO Command Object with Array Parameter

From: Chris Kettenbach (chris_at_piasd.org)
Date: 12/17/04 

Date: Thu, 16 Dec 2004 16:37:19 -0800

Has anyone had any success passing an adArray arg to a command object
parameter for an IN Clause?
As in "SELECT * FROM tmp1 WHERE MyID IN ?"

I successfully open the connection object, and I can append the param the
the parameters collection but I can not get the result I am looking for.

I am using VBScript

dim cmd
set cmd = createObject("ADODB.Command")

const adArray = xH2000
const adInteger = 5
const adParamInput = 1

dim a
    a(0) = 4
    a(1) = 5
    a(2) = 6

cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
adParamInput, 8, a))

I have tried it a million different ways. It appends to the collection just
fine if I leave off the Array, but as soon as I try to set the params value
to an Array, I get a wrong type error. I checked the field in the table and
Access lists it's value as Double. I thought it should have been a Long
Data Type. I have tried other values for adInteger, like adDouble, and a
couple others. Still nothing. I looked on the net and did not see any
Access databases getting this to work properly. Maybe it is not supported.
Anyways the desired result is to place the array in to the SQL statement IN
clause. Please point me in the right direction if anything is obvious.
Thanks to all who reply.

Regards,
Chris