CODEWITHSUNDEEP
sqlvb6ado
user3387046
user3387046

Reputation: 465

ADO passing an array to SQL statement

I'm trying to pass an array directly to a SQL statement. Is it possible to use MyArray() directly after the IN keyword instead of MyAr ?

Dim MyArray() As String
MyArray() = Array("YYYY", "BBB")
MyAr = "'YYYY','BBB'"
Set shiftrecordset = New ADODB.Recordset
strSQL1 = "SELECT * FROM assignements where shiftname in (" & MyArr & ")"
shiftrecordset.Open strSQL1, cn, adOpenKeyset, adLockOptimistic

Upvotes: 0

Views: 328

Answers (1)

Eliot
Eliot

Reputation: 91

That's not possible. You could write a function to do it instead though.

Private Function MakeIN(sArr() As String) As String
Dim i As Long
Dim lUB As Long

lUB = UBound(sArr)

For i = LBound(sArr) To lUB
    MakeIN = MakeIN & "'" & sArr(i) & "'"
    If i < lUB Then MakeIN = MakeIN & ","
Next i
End Function

Upvotes: 3

Related Questions