Connect to MySQL Database from VBA

Murray Brown
Murray Brown used Ask the Experts™
on
Hi
n
I have managed to connect to a SQL 2008 online databadse with the code in the code section
below. I need to write a similar function for a MySQL database residing on the same website.
I have written the following code which still doesn't work. Not sure why.
Sub oConnectToMySQL()

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
   

    'con.Open "Driver={MySQL ODBC 5.1 Driver};Server=196.220.41.175,3307;Database=mysql5;User=murbro; Password=chestnut;Option=3;"
        con.Open "Provider={MySQL ODBC 5.1 Driver};Data Source=196.220.41.175,3307;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"
    'mysql51c.[yourdomain.com] / Port: 3307 / Mysql IP: 196.220.41.175 phpmyadmin
   
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
   
    rst.Open "SELECT * FROM MyTable", con
   
End Sub
Sub ConnectToSQLServer()

    On Error GoTo EH
    'Assuming the SQL Express database is configured for remote connections you can. You generally do this by opening a connection to the database and then issuing your query:
    'Ref Microsoft AxtiveX Data Objects 6.0 Library
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"
     
    'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    '/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2
    
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM MyTable", con
    
    'These are the basics, but this would leave you with a fully filled Recordset.
    
    'Note that unless your online host has assigned a name for this, you'd have to use the full IP address as "Server" portion

Exit Sub

EH:
    MsgBox Err.Description
    
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

maybe this works for you:
Module to connect to MySQL

You will also find connection strings here:
Connection strings for MySQL

Cheers,

Christian
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks. Used the following code as set out on the article (see below) but still got the error:
[Microsoft].[ODBC Driver Manger] Data source name not found and no default driver specified

Option Compare Database

' put the following in a module

Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim username As String
Dim passwd As String
Dim serverIP As String
Dim db As String


Public Function connectMysql(username As String, passwd As String, serverIP As String, db As String, conn As ADODB.Connection, rs As ADODB.Recordset)
   
   Set conn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   conn.CursorLocation = adUseClient
   conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & serverIP & ";UID=" & username & ";PWD=" & passwd & ";DATABASE=" & db & ";" _
   & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
   conn.Open
   
End Function


Sub oConnectMySQL()
    On Error GoTo EH
   
    'mysql51c.[yourdomain.com] / Port: 3307 / Mysql IP: 196.220.41.175 phpmyadmin
   
    Call connectMysql("murbro", "chestnut", "196.220.41.175,3307", "mysql5", conn, rs)
   
    Dim ssql As String
    ssql = "SELECT * FROM Table1"
    rs.Open ssql, conn
   
    'Set DataGrid1.DataSource = rs
   
    Exit Sub
EH:
        MsgBox Err.Description
End Sub
Hi,

in your original post you used a 5.1 driver, you changed it to 3.51 here as was used in the mentioned article. You should adapt this to your 5.1 driver.

Moreover there are issues with 64 bit, maybe this thread is of help for you:

Data source name not found and no default driver specified

Cheers,

Christian
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial