vba实现mysql读取数据类型,从MySql数据库读取VBA脚本的问题

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

解决方案

MyODBC does not properly provide the RecordCount-Attribute.

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