Excel VBA connection to external MySQL server (ODBC 8.0)-CodePudding

I wanted to connect an excel file with a MySQL server for a project, so I searched the internet for a solution. I found some, but all of it was old and I don't prefer 8–10-year-old methods and drivers.
All I got is [Microsoft][ODBC Driver Manager] Data source name not found, and no default driver specified

So, I setted up the following setup:

A MySQL 8.0 server (on a Ubuntu server in the oracle cloud) 64-bit, ODBC connector 8.0 Unicode 64-bit
Office 365 64 bit

I tried to make a connection with some of the methods, but they gave all the same error: [Microsoft][ODBC Driver Manager] Data source name not found, and no default driver specified

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
    location = My server IP
    user = "ExcelTest"
    password = "Pass"
    database = "Test"
    mysql_driver = "MySQL ODBC 8.0 Driver" ''Tried "MySQL ODBC 8.0 Unicode Driver" too

    ''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
    Set OpenConnection = New ADODB.Connection
    OpenConnection.CursorLocation = adUseClient
    Call OpenConnection.Open(connectionString)

End Function

Usually I found a pretty good tutorial or walkthrou but this time nothing up to date.

I yes. I am pretty sure All the things are 64 bit.

I do not know where to search Please help

CodePudding user response：

Test the driver has installed correctly by using Windows ODBC Data Source Administrator first.

Option Explicit

Sub test()

    Dim conn
    Set conn = OpenConnection()
    With conn
        .CursorLocation = adUseClient
        MsgBox "Connected to " & .DefaultDatabase, vbInformation
    End With
    
End Sub

Public Function OpenConnection() As ADODB.Connection

    Const location = ""
    Const user = ""
    Const password = ""
    Const database = "test"
    Const mysql_driver = "MySQL ODBC 8.0 Unicode Driver"

    ' Build the connection string
    Dim s As String
    s = "Driver={" & mysql_driver & "};Server=" & _
    location & ";Database=" & _
    database & ";UID=" & _
    user & ";PWD=" & password
    Debug.Print s
    
    ' Open connection
    Set OpenConnection = New ADODB.Connection
    OpenConnection.Open s
    
End Function