在 SQL 语句中使用 VBA 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11609355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a VBA array in a SQL statement
提问by VBwhatnow
I am trying to write some code that uses SQL to delete rows from several tables.
我正在尝试编写一些使用 SQL 从多个表中删除行的代码。
A user would type type numbers into a textbox that are separated by a comma which is used in the WHERE clause of a SQL DELETE statement.
用户可以在文本框中键入数字，这些数字由逗号分隔，用于 SQL DELETE 语句的 WHERE 子句。
I have managed to split the string into a variant array and now I want to insert it into my SQL statement.
我已经设法将字符串拆分为一个变体数组，现在我想将它插入到我的 SQL 语句中。
How do I insert the variable into the SQL statement and have it run through every element of the array?
如何将变量插入 SQL 语句并让它遍历数组的每个元素？
EDIT: A bit more digging has taught me about For Each Next statements. This is probably what im looking for.
编辑：更多的挖掘教会了我关于 For Each Next 语句的知识。这可能就是我要找的。
回答by Fionnuala
I suggest you build your query in VBA, then your list of numbers can be an IN statement:
我建议您在 VBA 中构建查询，然后您的数字列表可以是 IN 语句：
sSQL = "DELETE FROM table WHERE ID In (" & MyList & ")"
Where MyList = "1,2,3,4" or such like.
其中 MyList = "1,2,3,4" 之类的。
As you can see, you do not need an array and a textbox would be more suitable than a combobox. If you wish to allow your users to select by say, a name, then a listbox is useful. You can iterate through the selected items in the listbox and build a string from IDs to be used in the Delete statement. ( MS Access 2007 - Cycling through values in a list box to grab id's for a SQL statement)
如您所见，您不需要数组，文本框比组合框更合适。如果您希望允许您的用户通过名称进行选择，那么列表框很有用。您可以遍历列表框中的选定项目，并根据 ID 构建一个字符串，以便在 Delete 语句中使用。（MS Access 2007 - 循环浏览列表框中的值以获取 SQL 语句的 ID）
You can then execute the sql against an instance of a database. For example:
然后，您可以针对数据库实例执行 sql。例如：
Dim db As Database
Set db = CurrentDB
db.Execute sSQL, dbFailOnError
MsgBox "You deleted " & db.RecordsAffected & " records."
回答by Madhivanan
A generic approach
通用方法
WHERE
','+Array+',' like '%,'+col+',%'
It will consider all the numbers available in your Array
它将考虑您的 Array 中可用的所有数字
回答by Jorge Aguirre
You could make it simple and elaborate a string, something like
你可以让它变得简单并详细说明一个字符串，比如
stringBuilder sb = StringBuilder("DELETE FROM YOURTABLE WHERE ");
foreach(string st in stringArray){
sb.append("YOURFIELD='" + st + "'");
//If it is not the last element, add an "OR"
if (st != stringArray[stringArray.length -1]) sb.append(" OR ");
}
//Now, you have a string like
//DELETE FROM YOURTABLE WHERE YOURFIELD='hello' OR YOURFIELD='YES'
//... do something with the command
回答by vbalite
This method will fail if you want to run SQL query on two (or multiple) columns using array values from two different arrays. .e.g
如果您想使用来自两个不同数组的数组值在两个（或多个）列上运行 SQL 查询，则此方法将失败。。例如
where col1=array1(i) and col2=array2(i)
