Connect MySQL(multiple instances) Database with Excel VBA

Question

I have multiple instances of MySQL installed in my pc which are instance1 & instance2...

• 1st instance data directory is "C:\MYSQL2\Data", address is: localhost, port= 3306;

• 2nd instance data directory is "C:\ProgramData\MySQL\MySQL Server 5.7\Data", address is: localhost, port= 3308;

server_name = "localhost"
port = "3308" 
database_name = "test2" 
user_id = "root" 
password = "xxxx" 

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";Data Source=localhost" _
& ";SERVER=" & server_name _
& ";PORT=" & port _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=16427" 

When i run the above code to connect with 2nd instance database it showing me error:

Run-time error '-2147467259(80004005)': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This code also showing error for my 1st instance which server name is localhost and port is=3306

This code running fine where only default instance is installed.

Answer 1

In the connection string if

Driver={MySQL ODBC 8.0 Unicode Driver}

then if you open ODBC data Sources (32-bit) not ODBC data Sources (64-bit)

see the same name

Then this code will work

Public Sub ask_sql1()
    Dim SQL As String
    SQL = "SELECT * FROM Users"
    
    Dim Conn As ADODB.Connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=127.0.0.1;Database=test;UID=root;PWD=*********"
    Conn.Open

    Dim recordSet As ADODB.recordSet
    Dim Field As ADODB.Field
    
    Set recordSet = New ADODB.recordSet
    recordSet.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
 
    If recordSet.State Then
        For Each Field In recordSet.Fields
            MsgBox Field.Name
        Next Field
        Set recordSet = Nothing
    End If
 
        Conn.Close

End Sub

also is you office 64 or 32

Can you add a screenshot of ODBC data Sources (32-bit)/Drivers ?