mysql - 使用 ADO.RecordSet 对象从 VBA 选择查询运行不返回完整结果

Question

我在本地主机上有一个 MySQL 数据库，我希望从 VBA 访问它。

我已经建立了与 MySQL 的 ODBC 连接，我可以查询结果。

目前MySQL表有2行数据需要返回。但是“Recordset.Fields”中的“Items”只保留最后一行。

我的代码如下

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

这是 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

请帮我弄清楚为什么没有保留整个查询结果。

谢谢

-清迈卡马特

Answer 1

这就是您通常编写此类操作的方式:

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