ADODB - VBA - MySQL - SHOW TABLES语法
问题描述:
我有通过VBA连接到MySQL数据库的连接,但我无法确定从SHOW TABLES查询返回值的正确语法。ADODB - 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
错误声明全文:
运行时错误“3265” - 项目无法对应于请求的名称或序号的集合 中找到。
这完全相同的代码工作完美,当我试图从“SHOW COLUMNS FROM tableName
”查询查看结果,也从一个“SELECT columnName1 FROM tableName
”查询。我认为错误是表名不应该返回为一个Recordset?
按照要求,这是我如何连接到我的数据库:
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
答
通过barrowc在评论中回答:
在一般情况下,你可以用ws.Cells(1, 1).CopyFromRecordset rs
替换整个For..Next循环,但不知道它是否有助于解决这个特定的错误 - barrowc Apr 8 at 23:54
一般来说,你可以用'ws.Cells(1,1).CopyFromRecordset rs'替换整个For..Next循环。不知道是否它可以帮助解决这个特定的错误 – barrowc
你告诉你如何connectDatabase? –
barrowc - 你的建议解决了我的问题!如果你把它作为答案,我可以将其标记为已接受! –