We help IT Professionals succeed at work.

Access VBA error connecting to online MySQL database

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I am trying to connect to my online MySQL database using the code below but anm getting the following error:
[MySQL][ODBC 5.1 Driver] Can’t connectto MySQL server ‘196.22041.175’(100600)
Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset



Public Sub connectMysql()
   
On Error GoTo EH
     
      'mysql51c.[yourdomain.com] / Port: 3307 / Mysql IP: 196.220.41.175 phpmyadmin
      
     Set conn = New ADODB.Connection
     conn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
     "SERVER=196.220.41.175;" & _
     "DATABASE=mysql5;" & _
     "USER=murbro;" & _
     "PASSWORD=chestnut;" & _
     "Option=3"
  
Exit Sub
EH:

MsgBox Err.Description

End Sub


Sub oConnectMySQL()
    On Error GoTo EH

    
    Call connectMysql
    
    Dim ssql As String
    ssql = "SELECT * FROM Table1"
    rs.Open ssql, conn
    
    'Set DataGrid1.DataSource = rs
    
    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®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
You don't have the port number attached to the server IP which is necessary because the standard port is 3306, not 3307.  Try "SERVER=196.220.41.175:3307;"

Do you know that that user is allowed remote access and that the server is open to remote access?
u missed port=3307 in your connection string, the default is 3306
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
When I change the line to
"SERVER=196.220.41.175:3307;"
I get the error
[MySQL][ODBC 5.1 Driver]Unknown MySQL server host ‘196.220.175:3307’(11004)
with the original line above trhe connection seemed to process longer as if it was going somewhere, now the error happens immediately
    Set conn = New ADODB.Connection
     conn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
     "SERVER=196.220.41.175;" & _
     "PORT=3307;" & _
     "DATABASE=mysql5;" & _
     "USER=murbro;" & _
     "PASSWORD=chestnut;" & _
     "Option=3"
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Excellent answer. It finally worked. Thanks very much