Select query run from VBA using ADO.RecordSet object does not return a complete result

Question

I have a MySQL DB on Localhost, which I wish to access from VBA.

I have set up the ODBC connection to MySQL, and I am able to query results.

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.

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:

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

Answer 1

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