How can VBA connect to MySQL database in Excel?

Question

Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
Dim str As String
str = "DRIVER={MySQL ODBC 5.2.2 Driver};" & _
                                            "SERVER=sql100.xtreemhost.com;" & _
                                            "PORT=3306" & _
                                            "DATABASE=xth_9595110_MyNotes;" & _
                                            "UID=xth_9595110;" & _
                                            "PWD=myPassword;" & _
                                            "Option=3"
''' error '''
oConn.Open str
End Sub

Private Sub InsertData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
sql = "SELECT * FROM ComputingNotesTable"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
    Range("A1").Select
    ActiveCell = rs.Fields("Headings")
    rs.MoveNext
Loop
rs.Close
oConn.Close
Set oConn = Nothing
Set rs = Nothing
End Sub

Doing the similar things in PHP, I could successfully log in to the MySQL server. I have installed the ODBC connector. But in the above VBA codes, I failed. An error turns up. (see the codes where the error exists)

$connect = mysql_connect("sql100.xtreemhost.com","xth_9595110","myPassword") or die(mysql_error());

mysql_select_db("myTable",$connect);

Jeroen · Accepted Answer · 2014-04-03 20:46:43Z

14

This piece of vba worked for me:

Sub connect()
    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement

    Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
    Server_Name = Range("b2").Value
    Database_name = Range("b3").Value ' Name of database
    User_ID = Range("b4").Value 'id user or username
    Password = Range("b5").Value 'Password

    SQLStr = "SELECT * FROM ComputingNotesTable"

    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 5.2.2 Driver};Server=" & _ 
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    Dim myArray()

    myArray = rs.GetRows()

    kolumner = UBound(myArray, 1)
    rader = UBound(myArray, 2)

    For K = 0 To kolumner ' Using For loop data are displayed
        Range("a5").Offset(0, K).Value = rs.Fields(K).Name
        For R = 0 To rader
           Range("A5").Offset(R + 1, K).Value = myArray(K, R)
        Next
    Next

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

edited Apr 3, 2014 at 20:46

Jeroen

64.4k47 gold badges231 silver badges372 bronze badges

answered Nov 26, 2012 at 5:12

Ranjit Kumar

7812 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Tin Amaranth Over a year ago

Thanks for your codes. But I have been stuck in which I don't know what the server should be. I'm using the MySQL of Xtreemhost and I have been using "sql100.xtreemhost.com". Xtreemhost Support says this is the name for the server. What should I do then?

Ranjit Kumar Over a year ago

have you tried placing your server name in the cell b2 of your excel sheet ??

Tin Amaranth Over a year ago

Yes, I did, supposedly that the server name is "sql100.xtreemhost.com" but for the driver, I wrote the codes like this: "Driver={MySQL ODBC 5.1.11 Driver}", according to the version on my machine. I was stuck in "Cn.Open" and the error is the same: "Run-time error -2147467259 (80004005) Automation error Unspecified error"

Ranjit Kumar Over a year ago

cn.open tells there is a error in connection and some times character will not be accepted !! after typing your server name in excel sheet cell b2 = sql100.xtreemhost.com edit the macro code- add a single code before to the server name --->Server='" & Server_Name & "'

Ranjit Kumar Over a year ago

have u created odbc datasource ?? and checked the testconnection??

|

Bob Davis · Accepted Answer · 2015-06-18 15:00:48Z

8

Ranjit's code caused the same error message as reported by Tin, but worked after updating Cn.open with the ODBC driver I'm running. Check the Drivers tab in the ODBC Data Source Administrator. Mine said "MySQL ODBC 5.3 Unicode Driver" so I updated accordingly.

answered Jun 18, 2015 at 15:00

Bob Davis

961 silver badge1 bronze badge

1 Comment

iliak Over a year ago

Even though I updated the driver I'm running, this didn't work for me until I activated Microsoft ActiveX Data Objects 6.1 Library. To do so, In VBE you need to go Tools/References and check Microsoft Active X Data Objects x library

aeroacoustic · Accepted Answer · 2015-11-07 12:57:27Z

5

Just a side note for anyone that stumbles onto this same inquiry... My Operating System is 64 bit - so of course I downloaded the 64 bit MySQL driver... however, my Office applications are 32 bit... Once I downloaded the 32 bit version, the error went away and I could move forward.

answered Nov 7, 2015 at 12:57

aeroacoustic

511 silver badge1 bronze badge

2 Comments

lukehawk Over a year ago

What driver? And where did you download it from? I am sorry, but I am new to trying to get excel to talk to MySQL. I have the same setup, with a 64bit machine and 32bit Office, so I suspect my problem may be similar to yours.

Paulo Bueno Over a year ago

Drivers can be found at MySQL Community Downloads page.

ChrCury78 · Accepted Answer · 2019-02-27 01:13:44Z

3

Updating this topic with a more recent answer, solution that worked for me with version 8.0 of MySQL Connector/ODBC (downloaded at https://downloads.mysql.com/archives/c-odbc/):

Public oConn As ADODB.Connection
Sub MySqlInit()
    If oConn Is Nothing Then
        Dim str As String
        str = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=xxxxx;DATABASE=xxxxx;PORT=3306;UID=xxxxx;PWD=xxxxx;"
        Set oConn = New ADODB.Connection
        oConn.Open str
    End If
End Sub

The most important thing on this matter is to check the proper name and version of the installed driver at: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers\

answered Feb 27, 2019 at 1:13

ChrCury78

4373 silver badges8 bronze badges

Comments

Andrew · Accepted Answer · 2012-11-26 05:13:22Z

1

Enable Microsoft ActiveX Data Objects 2.8 Library

Dim oConn As ADODB.Connection 
Private Sub ConnectDB()     
Set oConn = New ADODB.Connection    
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _        
"SERVER=localhost;" & _         
"DATABASE=yourdatabase;" & _        
"USER=yourdbusername;" & _      
"PASSWORD=yourdbpassword;" & _      
"Option=3" 
End Sub

There rest is here: http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/

answered Nov 26, 2012 at 5:13

Andrew

7,82813 gold badges68 silver badges120 bronze badges

2 Comments

Tin Amaranth Over a year ago

Have enabled the Data Object Library. And I wonder if SERVER=localhost, how can the application know my SQL server is on xtreemhost SQL? Still have the same error: "Run-time error -2147467259 (80004005) Automation error Unspecified error"

hammythepig Over a year ago

I tried this and got the Error: "Data source name not found and no default driver specified"

Jeppe Silas Nygaard · Accepted Answer · 2022-09-01 08:56:05Z

Just to update this further

instead of looping through every row and column which takes forever. try using

`Sub connect()
    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement

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

    SQLStr = "SELECT * FROM item"

    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 8.0 ANSI Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic
    Range("A2").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

this is multiple minutes faster

Collectives™ on Stack Overflow

How can VBA connect to MySQL database in Excel?

6 Answers 6

9 Comments

1 Comment

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

9 Comments

1 Comment

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related