在数组不为空的情况下，使用不同数组中的值运行SQL SELECT语句

Question

我正在尝试使用vba从Excel运行SQL语句。我所需要做的就是运行select语句并使用数组Array1和Array2中的值。Array1和Array2存储两列的WHERE条件。

例如

for i=0 to UBOUND(Array1) 'UBOUND for Array2 will always be equal to UBOUND for Array1
sql = "SELECT name, address, state, zip WHERE 
fname='" & Array1(i) & "' AND lname= '" & Array2(i) &"'" 
open rs.sql
sheets(1).range("A1").CopyFromRecordset rs
Next

似乎不起作用。我认为我没有正确使用循环。有人能指出我在这里做错了什么吗？

非常感谢您的调查。

编辑1:

连接参数：

以下是代码：

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

'database credentials
 Dim uName As String, uPass As String
 Dim strConnect As String
 strConnect = "Driver={SQL Server Native Client 10.0};" & "Server=[servername]" & "Database=[dbname] ;Uid=" & uname & ";Pwd=" & upass & ";trusted connection=yes"

 cn.ConnectionString = strConnect
 cn.Open
 for i=0 to UBOUND(Array1) 'UBOUND for Array2 will always be equal to UBOUND for Array1
 sql = "SELECT name, address, state, zip FROM XYZ WHERE 
 fname='" & Array1(i) & "' AND lname= '" & Array2(i) &"'" 
With rs
 .ActiveConnection = cn
 .Open sql
 ...[code to check for EOF/BOF, if not EOF or BOF then paste in pre defined cells]

 sheets(1).range("A1").CopyFromRecordset rs
Next

 end with
rs.close 
cn.close

Answer 1

我终于明白了。由于公式错误，数组1为空。一旦我解决了这个问题，我就可以运行查询了。其次，遵循循环中的构造：

cn.open
 for i=lbound(array1) to rbound(array2)
    sql="SELECT name, address, state, zip FROM XYZ WHERE fname='" & Array1(i) & "' AND lname= '" & Array2(i) &"'""
    rs.open sql, cn
    sheets(1).range("A2").copyfromrecordset rs
    rs.close
 next i
cn.close