MySQL Recordset Not Returning Values When it Should

Question

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:

I'm using Office 2007 32 Bit on Windows 7 64Bit. The MySQL server (5.6) is on Windows 8 64Bit.
The ODBC driver install is from the Oracle install mysql-connector-odbc-5.2.5-win32.msi. In the ODBC manager the driver name is displayed as per the code (there is also an ANSI driver which doesn't work either. The 64Bit ODBC drivers do not work with the 32Bit office install.
I've tried Referencing Microsoft ActiveX Data Objects 2.8 and 6.1.

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?

Not all providers (and not all cursor types) support the use of RecordCount. -1 is the default value when not supported. You should still be able to loop through the records though (can you show your code for that part?) — Tim Williams
– Tim Williams, Commented May 16, 2013 at 5:50
Thanks Tim, I was beginning to wonder if something like that was the case. I normally use something like Do While Not rst.EOF to loop through the records, but when the recordcount wasn't working I didn't move on to try the code! (It does of course work). So I guess the easiest way to get the recordcount is to return a single column on the query and do a count on what is returned from GetRows()? — CuberChase
– CuberChase, Commented May 16, 2013 at 6:07
If you only want the count then select count(*) from tableNameHere would be faster. — Tim Williams
– Tim Williams, Commented May 16, 2013 at 6:16

Community · Accepted Answer · 2017-05-23 11:51:12Z

4

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.

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered May 16, 2013 at 6:20

CuberChase

4,5185 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Recordset Not Returning Values When it Should

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related