Solved: Using an array as a parameter in an ADO query

[mferrisi]

I'm trying to use ArrayList as a parameter for the query, but when I use it as I do below, I get an error. However, if I replace ArrayList in the query with (335, 336, 337) the query works fine. How can I pass the array?

Thank you

Matt

[VBA]
Sub Test4()
Dim cnMTPS As ADODB.Connection
Dim cmd As ADODB.Command

Set rst = New ADODB.Recordset
Set cnMTPS = New ADODB.Connection

cnMTPS.ConnectionString = "DSN=MS Access Database;DBQ=P:\T Data\Master Data File.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

cnMTPS.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnMTPS
cmd.CommandType = adCmdText

ArrayList = Array(335, 336, 337)

cmd.CommandText = "SELECT Performance.ID, Performance.Date, Performance.Return FROM `P:\T Data\Master Data File for SE`.Performance Performance WHERE (Performance.ID in (ArrayList))"
Set rst = cmd.Execute
Sheet1.Range("A2").CopyFromRecordset (rst)
'cmd.CommandText = "DROP TABLE #ID"
'cmd.Execute
cnMTPS.Close
End Sub





[/VBA]

[Bob Phillips]

Not a chance, you need to provide the list as a string.

Or maybe dump it into a temporary table and compare against that table.

[mferrisi]

I was avoiding creating the string, since there are actually 60 parameters and not 3. Can I take the array that I have and dump it into a temporary table, and if so, what would that look like?

[mferrisi]

I can't seem to create a string that works. What would that look like?

[mferrisi]

This also gives me an error.

[VBA]
Dim v As Variant
v = Array(10, 23, 43, 54)
Dim t As Variant
t = "(" & Str(v(0)) & "," & Str(v(1)) & ")"

cmd.CommandText = "SELECT Performance.ID, Performance.Date, Performance.Return FROM `P:\T Data\Master Data File for SE`.Performance Performance " & _
"WHERE (Performance.ID in (t))"
Set rst = cmd.Execute

[/VBA]

[malik641]

Don't know if this will work, but I would try (using XLD's statement about using strings):
[VBA]Sub Test4()
Dim cnMTPS As ADODB.Connection
Dim cmd As ADODB.Command
Set rst = New ADODB.Recordset
Set cnMTPS = New ADODB.Connection

cnMTPS.ConnectionString = "DSN=MS Access Database;DBQ=P:\T Data\Master Data File.mdb;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
cnMTPS.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnMTPS
cmd.CommandType = adCmdText

ArrayList = Array(335, 336, 337)
cmd.CommandText = "SELECT Performance.ID, " & _
"Performance.Date, Performance.Return " & _
"FROM `P:\T Data\Master Data File for SE`.Performance Performance " & _
"WHERE (Performance.ID in (" & ArrayList & "))"
Set rst = cmd.Execute
Sheet1.Range("A2").CopyFromRecordset (rst)
'cmd.CommandText = "DROP TABLE #ID"
'cmd.Execute
cnMTPS.Close
End Sub[/VBA]

Notice how I took out "ArrayList" from the SQL string. This is so it can (hopefully) pass the contents of the array to the string you're building.

This is untested, btw.

[Bob Phillips]

This also gives me an error.

[vba]
Dim v As Variant
v = Array(10, 23, 43, 54)
Dim t As Variant
t = "(" & Str(v(0)) & "," & Str(v(1)) & ")"

cmd.CommandText = "SELECT Performance.ID, Performance.Date, Performance.Return FROM `P:\T Data\Master Data File for SE`.Performance Performance " & _
"WHERE (Performance.ID in (t))"
Set rst = cmd.Execute

[/vba]

This is untetsed, but I think it would be more like

[vba]

Dim v As Variant
v = Array(10, 23, 43, 54)
Dim t As Variant
t = "(" & Str(v(0)) & "," & Str(v(1)) & ")"

cmd.CommandText = "SELECT Performance.ID, Performance.Date, Performance.Return " & _
"FROM `P:\T Data\Master Data File for SE`.Performance Performance " & _
"WHERE (Performance.ID in (" & t & "))"
Set rst = cmd.Execute
[/vba]

As t is a variable, iot cannot be within the string, but must be substituted at run-time.