在SQL语句中使用VBA数组

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.

编辑:更多的挖掘已经教会我关于每一个陈述。这可能就是我要找的。

4 个解决方案

#1

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 )

如您所见，您不需要数组，文本框比组合框更合适。如果您希望允许用户选择名称，那么列表框是有用的。您可以在列表框中遍历所选的项，并从IDs中构建一个要在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."

#2

A generic approach

一个通用的方法

WHERE 
','+Array+',' like '%,'+col+',%'

It will consider all the numbers available in your Array

它将考虑数组中所有可用的数字

#3

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

#4

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)

#1