Excel VBA connects MySQL database to get data

Source: Internet
Author: User
Tags file copy odbc odbc connection

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.