Excel VBA connects MySQL database to get data

Write the Excel VBA tool to connect and manipulate the MySQL database.

System environment:

Os:win7 64-bit English version

Office 2010 32-bit English version

1, VBA before the preparation of the connection to MySQL

Tools--->references. ----> References

Tick Microsoft ActiveX Data Objects 2.8 Librarys and Microsoft ActiveX Data Objects Recordset 2.8 Librarys

2. Install MySQL ODBC connection service

Download Connection: HTTPS://DEV.MYSQL.COM/DOWNLOADS/CONNECTOR/ODBC

Note: The 32-bit version is used here

Download in Windows (x86,32-bit), Zip Archive version, in the test, using a 64-bit version cannot connect to MySQL

Download the above file copy into the Explorer, put it in the appropriate location, unzip, click on the install.bat batch file.

From the Control Panel---> Administrative Tools---->ODBC, after opening, you cannot see the MySQL driver from this, actually using the following 32-bit ODBC driver

Odbcca32.exe under the C:\Windows\SysWOW64\ folder

3. Connection examples

Sub Testconnecttodb ()    Dim conn as ADODB. Connection    Dim rs as ADODB. Recordset    Set conn = New ADODB. Connection    Set rs = New ADODB. Recordset    Conn. ConnectionString = "Driver={mysql ODBC 5.3 Unicode Driver}; Server=localhost; Port=3306;database=db_hptms; Uid=root; pwd=123456;option=3; "    Conn. Open    Rs. Open "Select Fdempno,fdempname from Tbuser", Conn with    thisworkbook.worksheets ("Mysqldata")         . Visible = True         . Range ("A1:b1"). Value = Array ("EmpNo", "EmpName")         . Range ("A2"). CopyFromRecordset rs         . Activate    End with    Rs. Close:set rs = Nothing    conn. Close:set conn = Nothing    MsgBox "Done", vbOKOnly, "Tip info" End Sub

The above example is tested to connect to the MySQL database properly, note that the version of ODBC connection used is 32-bit, in the test, using 64-bit, always can not open the connection normally.

Excel VBA connects MySQL database to get data