'Extract values from mySQL database into Excel using VBA

I have installed the mySQL server on my computer and created the Database called Sales consisting of one table called Orders. You can also find the table in the SQL fiddle here:

CREATE TABLE Orders (
    OrderID INT,
    Customer VARCHAR(255),
    Revenue VARCHAR(255)
);

INSERT INTO Orders
(OrderID, Customer, Revenue)
VALUES 
("1", "Customer A","400"),
("2", "Customer A","200"),
("3", "Customer B","600"),
("4", "Customer C","150"),
("5", "Customer C","800"),
("6", "Customer C","300");

Then I created an Excel file and activated Microsoft ActiveX Data Objects 2.8 Library.
Now I wanted to connect to the Database above and extract data from it using the following VBA script:

Sub ConnectDB()
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Sales; UID=root; PWD=mypassword; OPTION=3"

Set rs = New ADODB.Recordset
Set rs = conn.Execute("SELECT * FROM sales.orders;")
Sheet1.Range("A1").Value = rs

End Sub

However, when I run this VBA I get runtime error 3704. As far as I can tell this error is probably caused by these lines:

Set rs = New ADODB.Recordset
Set rs = conn.Execute("SELECT * FROM sales.orders;")

because if I delete them the VBA runs without the error.

What do I need to change in my code to extract the Data from the mySQL database?

mysqlexcelvba


Solution 1:[1]

your connection is not open

try this

    Sub ConnectDB()
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Sales; UID=root; PWD=mypassword; OPTION=3"
conn.open
Set rs = New ADODB.Recordset
Set rs = conn.Execute("SELECT * FROM sales.orders;")
Sheet1.Range("A1").Value = rs

End Sub

Solution 2:[2]

Don't use Execute - that is for running a command on the database, not for returning a recordset.

Sub ConnectDB()
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Sales; UID=root; PWD=mypassword; OPTION=3"
    conn.Open

    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM sales.orders;", conn
    Sheet1.Range("A1").Value = rs
End Sub

And if your string fields return what looks like garbage, you are going to have to do a little more than that too.

Solution 3:[3]

Try it like

Dim oConn As ADODB.Connection

Then 

    Private Sub ConnectDB()
Set oConn = New ADODB.Connection
Dim str As String

str = "DRIVER={MySQL ODBC 5.1 Driver};" & _
      "SERVER=XXX.XXX.XXX.XXX replace with your IP address" & ";" & _
      "PORT=replace with the port you are using, usually 3306" & ";" & _
      "DATABASE=replace with the name of your databese" & ";" & _
      "USER=replace with the username you are using to connetc yourself to the databes" & ";" & _
      "PASSWORD=replace with password you are using to connect to the databse" & ";" & _
      "Option=3"


oConn.Open str
End Sub

And Finally 

    Sub InsertData()

Dim Rs As ADODB.Recordset

Dim Requete As String

    Set Rs = New ADODB.Recordset
    Call ConnectDB
    Requete = "SELECT * FROM sales.orders;" 
    Rs.Open Requete, oConnect, adOpenDynamic, adLockOptimistic


    End With
    oConnect.Close
    Set Rs = Nothing
End Sub

Solution 4:[4]

Assuming you are using Excel 2000 or newer there is a surprisingly easy way to do this, and to even get the MySQL column names.

Public Sub insert_data()
    Dim dbConn As New ADODB.Connection, rs As New ADODB.Recordset, sql As String
    Dim ws As New Excel.Worksheet, cCtr As Long, numFields As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    dbConn.ConnectionString = "[put your connection string here]"
    dbConn.Open

    sql = "SELECT * FROM sales.orders;"
    Set rs = New ADODB.Recordset
    Set rs = dbConn.Execute(sql, , adCmdText)
    
    With ws
        ' Get the number of columns:
        numFields = rs.Fields.Count
        
        ' Put the column names into the first row of your worksheet:
        For cCtr = 1 To numFields
            .Cells(1, cCtr).Value = rs.Fields(cCtr - 1).name
        Next cCtr
        
        ' Put all the recordset data into your worksheet,
        ' starting from the worksheet's second row:
        .Cells(2, 1).CopyFromRecordset rs
    End With
    
    dbConn.Close
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 h2so4
Solution 2 braX
Solution 3 Luka AUDE
Solution 4 techjp

Related Questions