Excel VBA ADO 记录集 - 选择列的问题

Question

I get

运行时错误"-2147467259 (80004005("：未指定的错误

当我使用以下代码时：

Set conn1 = CreateObject("ADODB.Connection")
conn1.Open strcon1
strquery3 = "SELECT * FROM TABLE_NAME"
Set rs3 = CreateObject("ADODB.Recordset")
Set rs3 = conn1.Execute(strquery3)

但是，当我在 SELECT 语句中获取较少的列数时，代码有效：

Set conn1 = CreateObject("ADODB.Connection")
conn1.Open strcon1
strquery3 = "SELECT COLUMN1, COLUMN2 FROM TABLE_NAME"
Set rs3 = CreateObject("ADODB.Recordset")
Set rs3 = conn1.Execute(strquery3)

该表总共包含 17 列。列数有限制吗？如果没有，有什么原因无法获取记录集中的所有 17 列吗？

我正在连接到甲骨文数据库。使用的驱动程序 - "Microsoft ODBC for Oracle">

Answer 1

试试这个方式：

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim cs As String
Dim sql As String

' Initialize objects
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
' Create connection string and query string
cs = "Provider=SQLOLEDB; Data Source=<YOURSERVER>; Initial Catalog=<YOURDATABSE>; User ID=<YOURUSER>; Password=<YOURPASSWORD>"
sql = "select column1, column2 from table"
' Open connection and execute query
con.Open cs
cmd.CommandType = adCmdText
cmd.ActiveConnection = con
cmd.CommandText = sql
Set rs = cmd.Execute

然后，您可以访问列字段，例如：

Dim col1, col2 As String
col1 = rs.Fields("Column1").Value
col2 = rs.Fields("Column2").Value