excel vba mysql ado连接 [英] excel vba mysql ado connection

问题描述

我正在尝试在我的本地机器上的excel和我的服务器上的MySQL数据库之间建立一个ADO连接。

在我看到的例子中一个href =https://stackoverflow.com/questions/13558921/how-can-vba-connect-to-mysql-database-in-excel>这里和，使用ODBC列表中列出的MySQL驱动程序，并测试连接以查看它是否可行。

当您尝试从VBA中创建ODBC时，使用相同的参数。

示例：

  Driver = {MySQL ODBC 5.3 ANSI Driver}; Server = localhost; Database = myDataBase; 
 User = myUsername; Password = myPassword; Option = 3; 
  

6. 一旦建立，您可以成功创建连接到MySQL服务器，然后更改注册表中的驱动程序名称（请确保更新注册表项） ，并尝试使用您提供的新名称，例如 SQL Server 。

记住：在x64位系统上，使用x32位驱动程序：

  HKEY_LOCAL_MACHINE\ SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers 
  

重启更改驱动程序名称后可能需要。

I'm trying to establish an ADO connection between excel on my local machine and a MySQL database on my server.

In the examples I've seen (here and here, for instance) there's a driver of the form MySQL ODBC 5.x Driver. It seems that after installing the latest mysql connector / odbc download (32-bit, to match my msexcel) the relevant registry driver files HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Driver\ are now named 'SQL Server' and 'SQL Server Native Client 11.0.' I'm not having success establishing a connection to MySQL with either of these.

My VBA

Sub connect()
Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = "184.154.225.243"
Database_Name = "*******" ' Name of database
User_ID = "********" 'id user or username
Password = "*******" 'Password
Port = "3306"

SQLStr = "SELECT * FROM *******"

Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={SQL Server};Server=" & _
        Server_Name & ";Port=" & Port & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

Upon running the above, I receive error [Microsoft][ODBC SQL Server Drive][DBNETLIB]SQL Server does not exist or access denied. The error for the 'native client 11.0' driver is Could not open a connection to SQL Server[53].

I've tested the connection parameters in MySQL workbench and all is functional. What's going on?

解决方案

1. Check if you are using the 32 bit or the 64 bit version of Microsoft Office.

2. Based on the above, download and install the appropriate MySQL driver from the download link

3. Once the ODBC driver installation is complete, check the ODBC snap in to see the driver is listed as installed.

If you are using a 32 bit OS, then everything is 32 bit. Use Run -> odbcad32.exe -> Drivers tab.

If you are using a 64 bit OS, and Microsoft Office is 32 bit, then use c:\windows\syswow64\odbcad32.exe -> Drivers tab.

If you are using a 64 bit OS, and Microsoft Office is 64 bit, then use Run -> odbcad32.exe -> Drivers tab.

If the MySQL drivers are properly installed, they should appear as shown above

4. Create a System DSN using the ODBC snap in with the MySQL driver listed above and test the connection to see if it works.

5. Use the same parameters when you try to create an ODBC from within VBA.

Example:

Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=myDataBase;
User=myUsername;Password=myPassword;Option=3;

6. Once it is established that you can successfully create a connection to the MySQL server, then change the driver name in the registry (make sure to update both the registry keys) and try using the new name you give such as SQL Server.

Remember: On a x64 bit system for a x32 bit drivers:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers

A restart may be required after changing the driver name.

这篇关于excel vba mysql ado连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！