asked on

Access VBA error connecting to online MySQL database

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

Dave Baldwin

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?

raysonlee

u missed port=3307 in your connection string, the default is 3306

Murray Brown

ASKER

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

ASKER CERTIFIED SOLUTION

raysonlee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Excellent answer. It finally worked. Thanks very much