Couldn't find installable ISAM error when run my VBA code to record an item into SharePoint list

I recently encountered an issue again while using Excel 2016 and VBA to add new items to a SharePoint list for job tracking. After installing Excel and seemingly setting everything up correctly, I ran into an error message when I tried to execute my code.

找不到可安裝的 ISAM。

Upon pressing Debug, I discovered that the error occurred at the .Open line of my connection string. This is a common issue I’ve faced before, and I often forget how to resolve it. So, I decided to document my experience to help myself—and others—next time we encounter this error.

Here’s my VBA code for reference:

' Sub AddItem(i As Integer)
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim mySQL As String

    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
    mySQL = "SELECT * FROM [Your SharePoint List Name];"