VBA/SQL openrecordset(strSQL) 错误 3001
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5011834/
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
VBA/SQL openrecordset(strSQL) Error 3001
提问by Witcher
I am trying to run a query on a database server (Oracle 10g) using VBA/Excel and to display the results in a excel table. I have created the ODBC connection (read-only) and tested the connection. I works fine if I import data as a data source in excel (I am able to filter through a query as well) However my VBA code is giving me Error 3001
我正在尝试使用 VBA/Excel 在数据库服务器 (Oracle 10g) 上运行查询并将结果显示在 excel 表中。我已经创建了 ODBC 连接（只读）并测试了连接。如果我在 excel 中将数据作为数据源导入，我工作正常（我也可以通过查询进行过滤）但是我的 VBA 代码给了我错误 3001
Sub Test()
Dim cnn As ADODB.Connection
Dim canConnect As Boolean
Dim rs As Recordset
Dim strSQL As String
Set cnn = New ADODB.Connection
cnn.Open "DSN=blah;Uid=blah;Pwd=blah"
strSQL = "select job_start_dttm, job_end_dttm from c_job"
Set rs = cnn.openrecordset(strSQL)
ActiveCell = rs(0)
End Sub
I get Error 3001 - Arguemnts are of worng type, are out of acceptable range, or are in confilct with one another
我收到错误 3001 - 参数属于旧类型、超出可接受范围或相互冲突
The query itself runs fine in SQL developer. Thanks
查询本身在 SQL 开发人员中运行良好。谢谢
edit: The error is on "Set rs = cnn.openrecordset(strSQL)" line
编辑：错误在“Set rs = cnn.openrecordset(strSQL)”行
采纳答案by Fionnuala
Try:
尝试：
Sub Test()
Dim cnn As New ADODB.Connection
Dim canConnect As Boolean
Dim rs As New ADODB.Recordset
Dim strSQL As String
cnn.Open "DSN=blah;Uid=blah;Pwd=blah"
strSQL = "select job_start_dttm, job_end_dttm from c_job"
rs.Open strSQL, cnn
ActiveCell = rs(0)
End Sub
You seem to be mixing up a little DAO with your ADODB. You could have used Execute, but the above should suit.
您似乎将一些 DAO 与您的 ADODB 混为一谈。您可以使用 Execute，但以上应该适合。
回答by mellamokb
Try qualifying the type name of rs with the ADODB prefix to make sure it is not being defined as the built-in Access Recordset object type instead.
尝试使用 ADODB 前缀限定 rs 的类型名称，以确保它没有被定义为内置的 Access Recordset 对象类型。
Dim rs As ADODB.Recordset
Edit:
编辑：
You will also need to use the ADO .Execute command instead of the DAO .OpenRecordset:
您还需要使用 ADO .Execute 命令而不是 DAO .OpenRecordset：
Set rs = cnn.Execute("...")
