Excel VBA connecting to remote MySQL Database

Question

i am using the OBDC connector to use VBA to connect to my MySQL database. It currently runs on a local webserver (localhost) but is accessible from other PCs on the network via my PC's IP address.

In my connection function I had localhost as the location but when I change it to my IP address I get an

[MySQL][ODBC 5.2 Driver] (my computer name) is not allowed to connect to this MySQL server

error.

I presume this is a security problem. Any way to fix this?

Here is my connection function:

Public Function OpenConnection() As ADODB.connection
    //This function requires the "Microsoft ActiveX Data Objects" Library (Choose v2.8 from references for compatibility across Office versions)

    Dim source As String, location As String, user As String, password As String
    source = "MySQL"
    location = "192.168.1.60"
    user = "root"
    password = ""
    database = "database name"
    mysql_driver = "MySQL ODBC 5.2 ANSI Driver"

    //Build the connection string
    Dim connectionString As String

    connectionString = "Driver={" & mysql_driver & "};Server=" & location & ";Database=" & database & ";UID=" & user & ";PWD=" & password

    //Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    OpenConnection.CursorLocation = adUseClient
    Call OpenConnection.Open(connectionString)
End Function

pcreech pcreech · Accepted Answer · 2013-10-18T11:14:32

You need to modify the user account in MySQL. It is possible to modify the locations users are allowed to connect from and their credentials. Look at this post:

Allow all remote connections, MySQL

Excel VBA connecting to remote MySQL Database

1 Answers