Excel VBA：查询 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4305436/
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
Excel VBA: Query a MySQL Database
提问by Jonathan
I am looking for an example of how to query a MySQL database using Excel VBA.
我正在寻找如何使用 Excel VBA 查询 MySQL 数据库的示例。
I am able to use Data \ From Other Sources \ From Microsoft Queryto import data from the db, but what I am actually looking for is a way not to import it to a spreadsheet directly, but rather to a data structure in VBA for further manipulation before I output the result to the spreadsheet. How can I do this?
我可以使用Data \ From Other Sources \ From Microsoft Query从 db 导入数据，但我实际寻找的是一种不直接将其导入电子表格的方法，而是导入到 VBA 中的数据结构以进一步在将结果输出到电子表格之前进行操作。我怎样才能做到这一点？
回答by John M
to connect:
连接：
conMySQL.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=" & server & ";" & " DATABASE=" & database & ";" & "UID=" & login_user & ";PWD=" & password & "; OPTION=3; PORT=" & port & ";Connect Timeout=20;"
'open the connection
conMySQL.Open
then to query:
然后查询：
strSQL = "SELECT x FROM some_table"
MySQL.Query (strSQL)
With rsTemporary
Do Until .EOF
recordCount = recordCount + 1
some_variable = ![supcode]
rsTemporary.MoveNext
Loop
End With
MySQL.closeCon
回答by Dan
This works for me:
这对我有用：
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3" '
Set rs1 = New ADODB.Recordset
sqlstr = "SELECT * FROM `table1` WHERE `ID`=" & ID & ";"
rs1.Open sqlstr, conn, adOpenStatic
With Worksheets("Main").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
rs1.Close
Set rs1 = Nothing
