Thread: VBA array from SQL query

hi,

how do i pass an sql query into an array in vba

i have this so far:

rst.Open "Select [Package ID] as ids from ChannelBrandPackageLink WHERE [Channel Brand] = '" & Cells(r, 1) & "'", conn
 RecordCount = rst.RecordCount
 ReDim idt(RecordCount)
 idt = rst!ids
 rst.Close

however this seems to error with subscript out of range

thanks

Jonathan

Whether you need the Array or not was I think covered previously ?
(the recordset is to all intents and purposes an Array in it's own right)

However, if you want to, I'd repeat the code given previously

Dim idt As Variant
...
If rst.EOF = False Then idt = Application.Transpose(rst.GetRows)

No need to ReDim

It might be that you could just use a SubQuery directly ?

To answer your question... you could use Join

"WHERE [ID] IN (" & Join(Application.Transpose(idt),",") & ") AND ..."

If [ID] is not a numeric type then you would need to adjust the above to encase each item within ' eg:

"WHERE [ID] IN ('" & Join(Application.Transpose(idt),"','") & "') AND ..."

if you have apostrophe's within the ID's themselves then that would require addressing

"WHERE [ID] IN ('" & Replace(Replace(Join(Application.Transpose(idt),"^,^"),"'","''"),"^","'") & "') AND ..."