Problem with VBA script reading from MySql databas

Question

I am having some trouble with a vba script in Excel which should be reading from a MySql database. The SQL query should only return one record but actually returns an empty resultset. The generated statement works fine when run through phpMyAdmin.

Here is my code:

Function getClientId(emailAddress As String)
    Dim rs As ADODB.Recordset
    Dim sql As String

    ConnectDB

    Set rs = New ADODB.Recordset

    sql = "SELECT client_id FROM clients WHERE email_address = '" & emailAddress & "' LIMIT 1"
    Debug.Print sql
    rs.Open sql, oConn

    Debug.Print rs.RecordCount

    If (rs.RecordCount = -1) Then
        getClientId = Null
    Else
        getClientId = rs(0)
    End If
    rs.Close
End Function

EDIT: My database connect function.

Function ConnectDB()
    On Error GoTo ErrHandler

    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=mydb;" & _
    "USER=user;" & _
    "PASSWORD=password;" & _
    "Option=3"

    'Debug.Print oConn

    Exit Function
ErrHandler:
    MsgBox Err.Description, vbCritical, Err.Source
End Function

The ConnectDB function is connecting ok as I am running other scripts with it. If anyone can see what I am doing wrong then any help would be appreciated.

Many thanks in advance.

Garry

Answer 1

This could be problem of driver you have selected. My suggestion is (and I may be wrong) - if you are connecting via ODBC it could have different set of commands.

Answer 2

Use " (double quote) instead of ' (single quote), because you are querying through the ODBC driver.

Answer 3

MyODBC does not properly provide the RecordCount-Attribute.

Re: Problem with RecordCount with ASP & MySQL

rs.recordcount = -1 with myODBC

Re: ADO Connection RecordCount

So, if you really need the RecordCount, set CursorLocation Property to adUseClient. If not, just iterate through the RecordSet like this:

Do While Not rs.EOF
    '...do your magic
    rs.MoveNext
Loop