Problem with VBA script reading from MySql database

[kryten]

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

[Kenneth Hobs]

Welcome to the forum!

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

[mohanvijay]

change rs.open method like below

rs.Open Sql, oConn, adOpenStatic, adLockReadOnly

[kryten]

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.