Problem with VBA script reading from MySql database

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

Youre writing "query should only return one record" but don't say what actually happens - does it return more than one record or does it return empty resultset? In case of later I quess the emailAddress contains value which doesn't exist in the database. — ain
– ain, Commented Jul 21, 2011 at 10:39
Sorry guys, the sql returns an empty resultset where it should return one record with one field, the client id. I have run the SQL through phpMyAdmin and recieved the one record as expected. — Garry
– Garry, Commented Jul 21, 2011 at 10:53
@ain SELECT client_id FROM clients WHERE email_address = '[email protected]' LIMIT 1 — Garry
– Garry, Commented Jul 21, 2011 at 11:20
Instead of checking the recordcount, check for EOF instead. Record sets don't always report the correct count directly after opening them. — Tim Williams
– Tim Williams, Commented Jul 21, 2011 at 15:30

Jacob · Accepted Answer · 2011-07-22 08:36:54Z

2

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

answered Jul 22, 2011 at 8:36

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

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

1 Comment

Garry Over a year ago

Thanks cularis, that was the answer. I changed the check to see if rs(0) contained a vaue greater than zero and it worked fine.

Marijn · Accepted Answer · 2012-04-06 08:53:25Z

1

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

edited Apr 6, 2012 at 8:53

Marijn

10.6k5 gold badges62 silver badges81 bronze badges

answered Apr 5, 2012 at 20:40

sumana

111 bronze badge

Comments

Ondřej Holman · Accepted Answer · 2011-07-21 13:45:07Z

0

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.

answered Jul 21, 2011 at 13:45

Ondřej Holman

1486 bronze badges

1 Comment

Garry Over a year ago

Thanks for your reply Ondřej, I am using the MySQL ODBC 5.1 Driver. I have added the code to my post above.

Collectives™ on Stack Overflow

Problem with VBA script reading from MySql database

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related