从MySql数据库读取VBA脚本的问题 [英] Problem with VBA script reading from MySql database

问题描述

我在Excel中的vba脚本遇到麻烦，应该是从$ s
读取从MySql数据库。 SQL查询应该只返回一个
记录，但实际上返回一个空的结果集。生成的语句在运行phpMyAdmin时工作正常。

这是我的代码：

 函数getClientId（emailAddress As String）
 Dim rs As ADODB.Recordset 
 Dim sql As String 
 
 ConnectDB 
 
设置rs =新ADODB.Recordset 
 
 sql =SELECT client_id FROM clients WHERE email_address ='&电子邮件地址& 'LIMIT 1
 Debug.Print sql 
 rs.Open sql，oConn 
 
 Debug.Print rs.RecordCount 
 
如果（rs.RecordCount = -1）然后
 getClientId = Null 
 Else 
 getClientId = rs（0）
如果
 rs.Close 
结束函数
  

编辑：我的数据库连接功能。

 函数ConnectDB（）
错误GoTo ErrHandler 
 
设置oConn =新建ADODB.Connection 
 oConn.OpenDRIVER = {MySQL ODBC 5.1驱动程序};  &安培; _ 
SERVER = localhost; &安培; _ 
DATABASE = mydb; &安培; _ 
USER = user; &安培; _ 
PASSWORD = password; &安培; _ 
Option = 3
 
'Debug.Print oConn 
 
退出函数
 ErrHandler：
 MsgBox Err.Description，vbCritical， Err.Source 
结束函数
  

当我运行其他脚本时，ConnectDB函数正在连接好
与它。如果有人可以看到我做错了什么，那么任何帮助将
不胜感激。

非常感谢提前。

Garry

解决方案

MyODBC没有正确提供RecordCount-Attribute。

Re：使用ASP& MySQL

rs .recordcount = -1 with myODBC

Re：ADO Connection RecordCount

所以，如果你真的需要RecordCount，将CursorLocation属性设置为adUseClient。
如果没有，只需像这样遍历RecordSet：

  Do While Not rs.EOF 
' ...做你的魔法
 rs.MoveNext 
循环
  

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.

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

这篇关于从MySql数据库读取VBA脚本的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！