excelvbaodbcmysql-connectoradodb

Excel VBA ODBC reading field changes it to Null

Summary; The first time I read a data field, I get 0, every time after, the same record field reads Null. This changes if I debug, step through and watch the values in the locals/watchlist, I get Null every time.

This is in Excel VBA, using ADODB.Connection to a MySQL server on the same machine. I'm using the latest driver that I downloaded and installed from dev.mysql.com/downloads/connector/odbc

Here's the table fields and the first example row:

Field: meta_id post_id meta_key meta_value
Type: BIGINT UNSIGNED BIGINT UNSIGNED VARCHAR TEXT
Char Set: binary binary utf8mb4 utf8mb4
First Row: 21875 100 total_sales 0 
Sub test()
    Const sConnection As String = _
        "DRIVER={MySQL ODBC 9.2 Unicode Driver};" & _
        "SERVER=127.0.0.1;" & _
        "DATABASE=wp_woocommercedb;" & _
        "USER=root;" & _
        "PASSWORD=CorporateAnxiety;" & _
        "CharSet=utf8;"
    Const sSQLQuery = "SELECT * FROM `wp_postmeta` WHERE `post_id` = '100';"
    
    Dim conn As New ADODB.Connection
    conn.Open sConnection
    
    Dim rs As New ADODB.Recordset
    rs.Open sSQLQuery, conn
    
    Debug.Print rs.Fields("meta_id")
    Debug.Print rs.Fields("meta_value")
    Debug.Print rs.Fields("meta_id")
    Debug.Print rs.Fields("meta_value")
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

This is what it prints if I just run it:

 21875
0
 21875
Null

This is what I was expecting:

 21875
0
 21875
0

So I started poking around in the watch/Locals window. But when I step through looking at the value I get:

 21875
Null
 21875
Null

I've included screenshots of the value changing, because it's just so bizarre.

Screenshot showing the code in break mode paused at the first Debug.Print, and the meta_value is at "0"

Screenshot showing the code paused on the next line, and the meta_value has changed to Null

I've also tried having a look through connectionstrings.com/mysql which documents the connection options we're passing as text at the start. But no luck there.

To my mind the value shouldn't be changing. I can work around this if it's small and isolated. But I need to know if there's more that's glitching out. Any ideas what's going on?

Solution

  • Got it figured. Just had to change:

    rs.Open sSQLQuery, conn

    to:

    rs.Open sSQLQuery, conn, CursorType:=adOpenStatic

    So the third argument is a CursorTypeEnum. This defaults to adOpenForwardOnly. I've got a conjecture of what's going on under the hood. This 'forward cursor' is supposed to have the quickest read speed. I suspect that when the last field is read it's in some way incrementing to the next record set. Even viewing the last field in the Local/Watch window must count as a read, so the field was incrementing before I got to it.