Re: ODBC Excel Connection - Key Not Present

[Anthony Brinkhuis]

You need to configure the ODBC Driver first. If you go to the control panel > Administrative tools > ODBC Data Sources > Select User DSN > Add.... > Select MySQL ODBC 8.0 Unicode Driver > Set the Data Source Name > Set your TCP/IP Server > OK (unless you want to test the connection).

In Excel, go to the Data tab > Get Data > From ODBC > Select the "Data Source Name" you set earlier from the drop-down > OK > Set your database username and password > OK > Select what you want to import.

The database is not requested because it will pull down a list of databases available to the user. It will pull down all databases that are accessible at the host you set in the ODBC Data Sources.

If you want to us VB here is some code. You will need to clean it up a little, but it works well for me:
Sub db_get(strSQL As String)

On Error GoTo ErrorHandler

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Application.ScreenUpdating = False

' connect to MYSQL database
conn.Open "Provider=MSDASQL;Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & host & ";Database=" & database & ";User=" & user & ";Password=" & DecodeBase64(pass) & ";Option=3;"

Debug.Print Now & " | db_get: " & strSQL

rs.Open strSQL, conn, adOpenStatic, adLockReadOnly

' add the data to the worksheet 'Program Issues List' and table 'Action_Items'
Worksheets("Program Issues List").Range("Action_Items").CopyFromRecordset rs

rs.Close

conn.Close

' release the objects
Set rs = Nothing
Set conn = Nothing

Application.ScreenUpdating = True

Exit Sub

ErrorHandler:
' An error occurred while executing the query
MsgBox "db_get: An error occurred: " & err.Description
Debug.Print "db_get: An error occurred: " & err.Description
rs.Close
conn.Close
Application.ScreenUpdating = True

End Sub

Sub db_command(strSQL As String)

On Error GoTo ErrorHandler

Dim conn As New ADODB.Connection
Dim sql As String

conn.Open "Provider=MSDASQL;Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & host & ";Database=" & database & ";User=" & user & ";Password=" & DecodeBase64(pass) & ";Option=3;"

Debug.Print Now & " | db_command: database queried: " & strSQL

conn.Execute strSQL

conn.Close

Set conn = Nothing

Exit Sub

ErrorHandler:
' An error occurred while executing the query
MsgBox "db_command: An error occurred: " & err.Description
Debug.Print "db_command: An error occurred: " & err.Description
conn.Close

End Sub