Problem with VBA script reading from MySql database

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:

[vba]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[/vba]
My database connect function.
[vba]
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[/vba]
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

Welcome to the forum!

Maybe LCase() or UCase() is needed in the sql string? Try leaving the LIMIT off for testing.

change rs.open method like below

Code:

---

rs.Open Sql, oConn, adOpenStatic, adLockReadOnly

---

Thanks for the answers guys.

It turns out the answer was that rs.RecordCount was always returning -1 rather than the record count. By changing
[vba]If (rs.RecordCount = -1) Then
getClientId = Null
Else
getClientId = rs(0)
End If
[/vba] to
[vba]If (rs(0) < 1) Then
getClientId = Null
Else
getClientId = rs(0)
End If
[/vba] everything worked fine.