CODEWITHSUNDEEP
mysqlvbaadodbrecordset
CuberChase
CuberChase

Reputation: 4518

MySQL Recordset Not Returning Values When it Should

I'm trying to retrieve a recordset from MySQL through VBA (in Excel) using ADODB. I can successfully connect to the MySQL database over the network and can even return a list of the tables using ADOX, however when I try to return a recordset the recordcount is -1 and it is empty.

When I enter the same SQL statement into MySQL Workbench the result is returned as expected.

I have been successfully connecting to an Access database with ADO and now want to move that database to MySQL and have run into this problem.

Things to note:

This is the code I am using:

Sub TestMySQL()
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset

    'Set up the connection
    Set cnn = New ADODB.Connection
    cnn.Open "DRIVER={MySQL ODBC 5.2 Unicode Driver};" & _
        "SERVER=192.168.99.5;" & _
        "PORT=3307;" & _
        "DATABASE=MySQLDatabase;" & _
        "USER=username;" & _
        "PASSWORD=password;" & _
        "OPTION=3;"


    'Set up the recordset
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tbl_Test", cnn, adOpenDynamic, adLockReadOnly

    'Check the recordcount
    rst.MoveLast
    rst.MoveFirst
    If rst.RecordCount > 0 Then MsgBox "Success!"

Cleanup:
    On Error Resume Next
    If rst.State = adStateOpen Then rst.Close:        Set rst = Nothing
    If cnn.State = adStateOpen Then cnn.Close:        Set cnn = Nothing
End Sub

And for the record, the code that successfully returns all the tables within the database using the connection above.

Sub DisplayDBTables(cnn As Object)
  Dim ct As Object: Set ct = CreateObject("adox.Catalog")
  Dim tb As Object: Set tb = CreateObject("adox.Table")

  Set ct.ActiveConnection = cnn

  For Each tb In ct.Tables
    If tb.Type = "TABLE" Then Debug.Print tb.Name
  Next tb

  Set ct = Nothing: Set tb = Nothing
End Sub

Can anyone give an clues as to why I cannot return a recordset?

Edit: So using rst.GetRows() method works returning the result to an array. So I guess my question now is why can't I loop through the recordset and access each record like usually done with ADODB?

Upvotes: 2

Views: 8767

Answers (1)

CuberChase
CuberChase

Reputation: 4518

Tim's comment got me thinking and after searching non MySQL specific question I found this SO answer: https://stackoverflow.com/a/2032618/1733206

The trick was to make cursor client side. So add the following line after Set rst = New ADODB.Recordset.

rst.CursorLocation = adUseClient

And as a further note, querying the record count directly from the database as per Tim's comment (ie SELECT Count(*) FROM tbl_Name) will be quicker over larger datasets than the MoveLast, RecordCount combo.

Upvotes: 4

Related Questions