PDA

View Full Version : Solved: Using an array as a parameter in an ADO query



mferrisi
06-12-2007, 08:39 AM
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


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

Bob Phillips
06-12-2007, 08:41 AM
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
06-12-2007, 09:01 AM
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
06-12-2007, 09:06 AM
I can't seem to create a string that works. What would that look like?

mferrisi
06-12-2007, 03:31 PM
This also gives me an error.


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

malik641
06-12-2007, 04:15 PM
Don't know if this will work, but I would try (using XLD's statement about using strings):
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

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
06-13-2007, 01:01 AM
This also gives me an error.


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



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



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


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