VBA 中的错误：`[Microsoft][ODBC Manager] 未找到数据源名称且未指定默认驱动程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5455078/
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
Error in VBA:`[Microsoft][ODBC Manager] Data source name not found and no default driver specified
提问by Parth Bhatt
I am creating a macro to add data from my Excel sheet into my MySQL Database
我正在创建一个宏来将 Excel 工作表中的数据添加到我的 MySQL 数据库中
But when I run the Macro I am getting Error:
但是当我运行宏时，我收到错误：
[Microsoft][ODBC Manager] Data source name not found and no default driver specified
[Microsoft][ODBC Manager] Data source name not found and no default driver specified
Code:
代码：
Sub UpdateMySQLDatabasePHP()
' For detailed description visit http://www.vbaexcel.eu/
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = Range("e4").Value ' IP number or servername
Database_Name = Range("e1").Value ' Name of database
User_ID = Range("h1").Value 'id user or username
Password = Range("e3").Value 'Password
Tabellen = Range("e2").Value ' Name of table to write to
rad = 0
While Range("a6").Offset(rad, 0).Value <> tom
TextStrang = tom
kolumn = 0
While Range("A5").Offset(0, kolumn).Value <> tom
If kolumn = 0 Then TextStrang = TextStrang & Cells(5, 1) & " = '" & Cells(6 + rad, 1)
If kolumn <> 0 Then TextStrang = TextStrang & "', " & Cells(5, 1 + kolumn) & " = '" & Cells(6 + rad, 1 + kolumn)
kolumn = kolumn + 1
Wend
TextStrang = TextStrang & "'"
field2 = "cid"
field1 = "bid"
table1 = "MMbanner"
SQLStr = "UPDATE " & Tabellen & " SET " & TextStrang & "WHERE " & Cells(5, 1) & " = '" & Cells(6 + rad, 1) & "'"
Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
Cn.Execute SQLStr
rad = rad + 1
Wend
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
采纳答案by Nathan Fisher
It looks as though there is something wrong with the connection string. do you have the mySQL odbc driver installed?
看起来好像连接字符串有问题。您是否安装了 mySQL odbc 驱动程序？
the easiest way I have found to test connections is to create a text file "New Text Document.txt" and renaming the file extension to udl so you end up with "New Text Document.udl" then open the file. It should show you the Datalink proerties wizard. you can then go through the wizard to create and test the connection. to get the connection string. either open the ".udl" file with notepad or change the extension back to ".txt" and open it with notepad.
我发现测试连接的最简单方法是创建一个文本文件“New Text Document.txt”并将文件扩展名重命名为 udl，这样您最终会得到“New Text Document.udl”，然后打开该文件。它应该向您显示 Datalink 属性向导。然后，您可以通过向导来创建和测试连接。获取连接字符串。要么用记事本打开“.udl”文件，要么将扩展名改回“.txt”并用记事本打开。
