VBA ADODB Connection String with Port

Question

I am working on an Excel VBA project where I connect to a MySQL server using ADODB, but I can't figure out how to add the port information to the connection string. My connection code works as I've connected to other DB's, but I recently moved to my local computer where I have multiple connections with each residing on a different port. Here is my current connection string:

''''''''''''''''''''''''''''''''''''''''''''''''
' My Laptop Connection
''''''''''''''''''''''''''''''''''''''''''''''''
Public Const server_name As String = "127.0.0.1:5353"    'Enter your    server name here - if running from a local computer use 127.0.0.1 or localhost
Public Const database_name As String = "juice"      'Enter your database name here
Public Const user_id As String = "root"             'Enter your user ID here
Public Const password As String = "Password1"         'Enter your password here

Public Const MySQLConnectStr As String = "DRIVER={MySQL ODBC 5.3 ANSI Driver}" _
                                    & ";SERVER=" & server_name _
                                    & ";DATABASE=" & database_name _
                                    & ";UID=" & user_id _
                                    & ";PWD=" & password _
                                    & ";OPTION=16427"

Did you check this? Ref: ConnectionStrings.com

Techie
– Techie

2015-12-06 20:32:18 +00:00
Commented Dec 6, 2015 at 20:32 — Techie
– Techie, Commented Dec 6, 2015 at 20:32
I just checked that out right. Thank you!

Stephen Day
– Stephen Day

2015-12-06 20:43:01 +00:00
Commented Dec 6, 2015 at 20:43 — Stephen Day
– Stephen Day, Commented Dec 6, 2015 at 20:43

Stephen Day · Accepted Answer · 2015-12-06 20:41:22Z

Okay, I figured it out over on connectionstrings.com - http://www.connectionstrings.com/mysql-connector-odbc-5-2/

You supply the port in a separate argument. See my fixed code below.

''''''''''''''''''''''''''''''''''''''''''''''''
' My Laptop Connection
''''''''''''''''''''''''''''''''''''''''''''''''
Public Const server_name As String = "127.0.0.1"    'Enter your server name here - if running from a local computer use 127.0.0.1 or localhost
Public Const database_name As String = "juice"      'Enter your database name here
Public Const user_id As String = "root"             'Enter your user ID here
Public Const password As String = "Password1"         'Enter your password here
Public Const port As String = "5353"                'If a specific port enter here. connection string uses 3306 by default.

Public Const MySQLConnectStr As String = "DRIVER={MySQL ODBC 5.3 ANSI Driver}" _
                                    & ";SERVER=" & server_name _
                                    & ";PORT=" & port _
                                    & ";DATABASE=" & database_name _
                                    & ";UID=" & user_id _
                                    & ";PWD=" & password _
                                    & ";OPTION=16427"

Collectives™ on Stack Overflow

VBA ADODB Connection String with Port

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related