使用 ADO.RecordSet 对象从 VBA 运行选择查询不返回完整结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25698032/
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
Select query run from VBA using ADO.RecordSet object does not return a complete result
提问by Chinmay Kamat
I have a MySQL DB on Localhost, which I wish to access from VBA.
我在 Localhost 上有一个 MySQL 数据库，我希望从 VBA 访问它。
I have set up the ODBC connection to MySQL, and I am able to query results.
我已经建立了到 MySQL 的 ODBC 连接，我可以查询结果。
Presently, the MySQL table has 2 rows of data which should be returned. But the "Items" in "Recordset.Fields" is retaining only the last row.
目前，MySQL 表有 2 行应返回的数据。但是“Recordset.Fields”中的“Items”只保留最后一行。
My code is as follows
我的代码如下
Public Sub Query_()
Dim connection As connection
Set connection = OpenConnection()
' Create a record-set that holds all the tasks
Dim records As ADODB.Recordset
Set records = New ADODB.Recordset
Call records.Open("SELECT pk_Client, PAN_Client FROM client", connection)
Dim result() As String
For Each Item In records.Fields
MsgBox (Item.OriginalValue)
Next
connection.Close
End Sub
Here is the OpenConnection UDF:
这是 OpenConnection UDF：
Private Function OpenConnection() As ADODB.connection
'Read type and location of the database, user login and password
Dim source As String, location As String, user As String, password As String
source = "taskman"
location = "localhost"
user = "root"
password = ""
'Build the connection string depending on the source
Dim connectionString As String
connectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & location & ";Database=taskman;UID=" & user & ";PWD=" & password
'Create and open a new connection to the selected source
Set OpenConnection = New ADODB.connection
Call OpenConnection.Open(connectionString)
End Function
Please help me in figuring out why the entire query result is not being retained.
请帮我弄清楚为什么没有保留整个查询结果。
Thanks
谢谢
-Chinmay Kamat
-Chinmay Kamat
采纳答案by Tim Williams
This is how you'd typically code this sort of operation:
这是您通常对此类操作进行编码的方式：
Public Sub Query_()
Dim conn As ADODB.Connection
Dim records As ADODB.Recordset, fld As ADODB.Field
Set conn = OpenConnection()
Set records = New ADODB.Recordset
records.Open "SELECT pk_Client, PAN_Client FROM client", conn
'check you got any records
If Not records.EOF Then
'loop over records
Do While Not records.EOF
Debug.Print "-------------------------"
For Each fld In records.Fields
Debug.Print fld.Name, fld.OriginalValue
Next
records.movenext 'next record
Loop
End If
records.Close
conn.Close
End Sub
