How VBA connect MySQL database and run a simple query

[mabalo76]

I need to use Excel VBA connect MySQL database and run a simple query to get a count(*) from one table. But I don't know how to create a connection and run this query. Would anyone please help?

Simple query just like below:
select Count(*) from Table1 where Table1.date is not null;

 

[mabalo76]

I have figured it out. My OS is windows 7
1. you need to install visual Studio 2015 Redistributable
2. download and install MySQL connector/ODBC
3. setup the ODBC in "ODBC Datasource Administrator" in windows

Then connect the database using below code. you have to input your server(ip address), database(your database name), user and password.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={MySQL ODBC 8.0 ANSI Driver};Server=; Database=; UID=; PWD="
cn.Open strConn


After connecting the database, you can run any query you want

 

[GeorgeAustralia]

I have figured it out. My OS is windows 7
1. you need to install visual Studio 2015 Redistributable
2. download and install MySQL connector/ODBC
3. setup the ODBC in "ODBC Datasource Administrator" in windows

Then connect the database using below code. you have to input your server(ip address), database(your database name), user and password.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={MySQL ODBC 8.0 ANSI Driver};Server=; Database=; UID=; PWD="
cn.Open strConn


After connecting the database, you can run any query you want

Thank you mabalo76.
I found your post (from 2018) useful.

Just in case any one needs the ODBC driver, it's here:

MySQL :: Download Connector/ODBC

dev.mysql.com

In my case I downloaded this file: mysql-connector-odbc-8.0.30-winx64.msi
which is for Windows .
You can choose different OS also (Linux and MacOS).

In terms of the VBA code, I took mabalo76's code and added a few lines :

Sub test()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={MySQL ODBC 8.0 ANSI Driver};Server=localhost; Database=XXXXXXXXXXXXXXXXXX; UID=root; PWD=root"
cn.Open strConn

    '' updSql = "SELECT * FROM YYYYYYYYYYYYYYYYYYYYYYYY "    
    updSql = "UPDATE YYYYYYYYYYYYYYYYYYYYYYYY SET VERSION='5.0' WHERE VERSION='4.0'"
    
    cn.Execute updSql
    
    
MsgBox "Done"

End Sub

Replace XXXXXXXXXXXXXXXXXX with the name of our MySQL database name.
Replace root/root with your login ID and password for the MySQL.
Replace YYYYYYYYYYYYYYYYYYYYYYYY with your Table name.

In my case I tested with the MySQL column called VERSION.
I test updated its value, changing it from 4.0 to 5.0
And the above VBA code worked.

My goal is to automatically duplicate Outlook Calendar appointments (be it newly created or modified) into MySQL.
I figured Excel VBA -> MySQL is more common, and tutorials are more plentiful.
I expect the above code should also work with Outlook VBA, which is what I need.

Best wishes.