Excel VBA: Query a MySQL Database

Question

I am looking for an example of how to query a MySQL database using Excel VBA.

I am able to use Data \ From Other Sources \ From Microsoft Query to import data from the db, but what I am actually looking for is a way not to import it to a spreadsheet directly, but rather to a data structure in VBA for further manipulation before I output the result to the spreadsheet. How can I do this?

As in: http://stackoverflow.com/questions/2821718/excel-vba-writing-to-mysql-database/2821911#2821911 ? — Fionnuala, Nov 29 '10 at 16:46
@Remou - not quite, I was looking for an example how to query and receive the result — Jonathan Livni, Dec 01 '10 at 14:46

score 4 · Accepted Answer · answered Nov 30 '10 at 15:29

to connect:

conMySQL.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=" & server & ";" & " DATABASE=" & database & ";" & "UID=" & login_user & ";PWD=" & password & "; OPTION=3; PORT=" & port & ";Connect Timeout=20;"

    'open the connection
    conMySQL.Open

then to query:

strSQL = "SELECT x FROM some_table"
MySQL.Query (strSQL)

With rsTemporary
      Do Until .EOF
          recordCount = recordCount + 1
          some_variable = ![supcode]
          rsTemporary.MoveNext
      Loop
End With
        MySQL.closeCon

Which are the data types of MySQL and conMySQL? – César Rodriguez Jan 08 '21 at 23:14 — César Rodriguez, Jan 08 '21 at 23:14

score 0 · Answer 2 · answered Nov 04 '15 at 21:30

This works for me:

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=3" '

Set rs1 = New ADODB.Recordset
sqlstr = "SELECT * FROM `table1` WHERE `ID`=" & ID & ";" 
rs1.Open sqlstr, conn, adOpenStatic
With Worksheets("Main").Cells(1, 1)
.ClearContents
.CopyFromRecordset rs1
End With
rs1.Close
Set rs1 = Nothing

Excel VBA: Query a MySQL Database

2 Answers2

Linked