vba MySQL 记录集不应该返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16579216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Recordset Not Returning Values When it Should
提问by CuberChase
I'm trying to retrieve a recordset from MySQL through VBA (in Excel) using ADODB. I can successfully connect to the MySQL database over the network and can even return a list of the tables using ADOX, however when I try to return a recordset the recordcount is -1 and it is empty.
我正在尝试使用 ADODB 通过 VBA（在 Excel 中）从 MySQL 检索记录集。我可以通过网络成功连接到 MySQL 数据库，甚至可以使用 ADOX 返回表列表，但是当我尝试返回记录集时，记录计数为 -1 并且为空。
When I enter the same SQL statement into MySQL Workbench the result is returned as expected.
当我在 MySQL Workbench 中输入相同的 SQL 语句时，结果按预期返回。
I have been successfully connecting to an Access database with ADO and now want to move that database to MySQL and have run into this problem.
我已经使用 ADO 成功连接到 Access 数据库，现在想将该数据库移动到 MySQL 并遇到了这个问题。
Things to note:
注意事项：
- I'm using Office 2007 32 Bit on Windows 7 64Bit. The MySQL server (5.6) is on Windows 8 64Bit.
- The ODBC driver install is from the Oracle install mysql-connector-odbc-5.2.5-win32.msi. In the ODBC manager the driver name is displayed as per the code (there is also an ANSI driver which doesn't work either. The 64Bit ODBC drivers do not work with the 32Bit office install.
- I've tried Referencing Microsoft ActiveX Data Objects 2.8 and 6.1.
- 我在 Windows 7 64 位上使用 Office 2007 32 位。MySQL 服务器 (5.6) 在 Windows 8 64Bit 上。
- ODBC 驱动程序安装来自 Oracle 安装 mysql-connector-odbc-5.2.5-win32.msi。在 ODBC 管理器中，驱动程序名称按照代码显示（还有一个 ANSI 驱动程序也不起作用。64 位 ODBC 驱动程序不适用于 32 位办公安装。
- 我尝试过引用 Microsoft ActiveX 数据对象 2.8 和 6.1。
This is the code I am using:
这是我正在使用的代码：
Sub TestMySQL()
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
'Set up the connection
Set cnn = New ADODB.Connection
cnn.Open "DRIVER={MySQL ODBC 5.2 Unicode Driver};" & _
"SERVER=192.168.99.5;" & _
"PORT=3307;" & _
"DATABASE=MySQLDatabase;" & _
"USER=username;" & _
"PASSWORD=password;" & _
"OPTION=3;"
'Set up the recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tbl_Test", cnn, adOpenDynamic, adLockReadOnly
'Check the recordcount
rst.MoveLast
rst.MoveFirst
If rst.RecordCount > 0 Then MsgBox "Success!"
Cleanup:
On Error Resume Next
If rst.State = adStateOpen Then rst.Close: Set rst = Nothing
If cnn.State = adStateOpen Then cnn.Close: Set cnn = Nothing
End Sub
And for the record, the code that successfully returns all the tables within the database using the connection above.
作为记录，使用上面的连接成功返回数据库中所有表的代码。
Sub DisplayDBTables(cnn As Object)
Dim ct As Object: Set ct = CreateObject("adox.Catalog")
Dim tb As Object: Set tb = CreateObject("adox.Table")
Set ct.ActiveConnection = cnn
For Each tb In ct.Tables
If tb.Type = "TABLE" Then Debug.Print tb.Name
Next tb
Set ct = Nothing: Set tb = Nothing
End Sub
Can anyone give an clues as to why I cannot return a recordset?
任何人都可以提供有关为什么我无法返回记录集的线索吗？
Edit:So using rst.GetRows()method works returning the result to an array. So I guess my question now is why can't I loop through the recordset and access each record like usually done with ADODB?
编辑：所以使用rst.GetRows()方法可以将结果返回到数组。所以我想我现在的问题是为什么我不能像通常使用 ADODB 那样循环遍历记录集并访问每个记录？
回答by CuberChase
Tim's comment got me thinking and after searching non MySQL specific question I found this SO answer: https://stackoverflow.com/a/2032618/1733206
蒂姆的评论让我思考，在搜索非 MySQL 特定问题后，我找到了这个答案：https: //stackoverflow.com/a/2032618/1733206
The trick was to make cursor client side. So add the following line after Set rst = New ADODB.Recordset.
诀窍是使游标客户端。所以在Set rst = New ADODB.Recordset.
rst.CursorLocation = adUseClient
And as a further note, querying the record count directly from the database as per Tim's comment (ie SELECT Count(*) FROM tbl_Name) will be quicker over larger datasets than the MoveLast, RecordCountcombo.
作为进一步的说明，根据 Tim 的评论（即SELECT Count(*) FROM tbl_Name）直接从数据库查询记录计数将比MoveLast,RecordCount组合更快地处理更大的数据集。
