Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Connect to MySQL Database from VBA

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

Microsoft AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Bitsqueezer

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 Brown

ASKER
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
ASKER CERTIFIED SOLUTION
Bitsqueezer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Murray Brown

ASKER
thanks very much for the help
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck