MySQL Forums
Forum List  »  Connector/ODBC

Re: ODBC Excel Connection - Key Not Present
Posted by: Anthony Brinkhuis
Date: January 05, 2023 12:58PM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: ODBC Excel Connection - Key Not Present
January 05, 2023 12:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.