mysql - ADODB - VBA - MySQL - 显示表语法

Question

我通过 VBA 连接到 MySQL 数据库，但我无法确定从 SHOW TABLES 查询返回值的正确语法。

Dim rs As Object
Dim ws As Worksheet
Dim sqlstr As String
Set rs = CreateObject("ADODB.Recordset")
Set ws = ThisWorkbook.Worksheets(1)  
sqlstr = "SHOW TABLES"

Call connectDatabase
rs.Open sqlstr, DBCONT

For i = 0 To (rs.RecordCount - 1)
    ws.Cells(i+1, 1).value = rs(i)
    rs.movenext
Next i

rs.Close
Set rs = Nothing
Call closeDatabase

错误语句如下:

Run-time error '3265' - Item cannot be found in the collection corresponding to the requested name or ordinal.

当我试图查看“SHOW COLUMNS FROM tableName”查询以及“SELECT columnName1 FROM tableName”的结果时，这段完全相同的代码工作得很好询问。我假设错误是表名不应该作为记录集返回？？？

根据要求，这是我连接到数据库的方式:

Public DBCONT As Object

Public Function connectDatabase()
    Set DBCONT = CreateObject("ADODB.Connection")

    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim Port As String
    Dim sConn As String

    Server_Name = "localhost"
    Database_Name = "databaseName"
    User_ID = "userID"
    Password = "password"
    Port = "3306"

    sConn = "Driver={MySQL ODBC 5.1 Driver};Server=" & _
                Server_Name & ";Database=" & Database_Name & _
                ";UID=" & User_ID & ";PWD=" & Password & ";Option=3;"

    DBCONT.Open sConn
    DBCONT.cursorlocation = 3

End Function

这是我关闭数据库的方式:

Public Function closeDatabase()
    On Error Resume Next 
    DBCONT.Close
    Set DBCONT = Nothing
    On Error GoTo 0
End Function

Answer 1

barrowc 在评论中的回答:

通常，您可以将整个 For..Next 循环替换为 ws.Cells(1, 1).CopyFromRecordset rs 但不确定它是否有助于解决此特定错误 – barrowc 4 月 8 日在 23:54