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

Community · Accepted Answer · 2017-05-23 12:13:58Z

1

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

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Oct 18, 2013 at 11:14

pcreech

3341 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

harryg Over a year ago

Just discovered it was a user account issue and have added a new user. Many thanks

b1919676 Over a year ago

@harryg I'm facing the same issue as you did, but I can't resolve it despite reading the link given by pcreech. Please, do you mind to elaborate how did you get it working?

Collectives™ on Stack Overflow

Excel VBA connecting to remote MySQL Database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related