错误VBA：`[微软] [ODBC管理器]数据源名称找不到和未指定默认驱动程序

Question

我创建一个宏从我的Excel工作表添加数据到我的MySQL数据库

但是当我运行宏我得到错误：

[Microsoft][ODBC Manager] Data source name not found and no default driver specified

代码：

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 

Answer 1

看起来好像疗法e连接字符串有问题。你有没有安装mySQL odbc驱动程序？

我发现测试连接的最简单方法是创建一个文本文件“New Text Document.txt”，并将文件扩展名重命名为udl，以便最终生成“New Text Document.udl”，然后打开该文件。它应该向您展示Datalink proerties向导。然后您可以通过向导来创建和测试连接。获取连接字符串。用记事本打开“.udl”文件或将扩展名改回“.txt”并用记事本打开。