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];"

    With cnt ' See https://www.connectionstrings.com/sharepoint/
.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://yoursharepointsite.sharepoint.com/sites/ABC/;LIST={xxxx-xxxx-xxxx-xxxx-xxx...};"
        .Open
    End With

    rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic
    rst.AddNew
        rst.Fields("完成日期") = Date
        rst.Fields("BU") = Trim(Cells(i, 1).Value)
        rst.Fields("Project") = Trim(Cells(i, 2).Value)
        rst.Fields("料號") = Trim(Cells(i, 3).Value)
        rst.Fields("BOMRevision") = Trim(Cells(i, 4).Value)
        rst.Fields("PCBRevision") = Trim(Cells(i, 5).Value)
        rst.Fields("FTP_Path") = Cells(i, 15).Value
        rst.Fields("File_Size") = Cells(i, 9).Value
    rst.Update ' commit changes to SP list

    If CBool(rst.State And adStateOpen) = True Then rst.Close
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    MsgBox "請確認是否正常寫進M365 Sharepoint內的清單中~"
End Sub

But first, make sure you have activated the necessary object library references. If you’re experiencing errors related to ADODB connections or ODBC connectors, my solution might help.

Solution:

Download the Microsoft Access Database Engine 2016 Redistributable and install the version that matches your Office installation (32-bit or 64-bit).

Microsoft Access Database Engine 2016 可轉散發套件

This has resolved my connection issues almost every time.

每次重裝電腦，設置環境，整個測試時總有五花八門的問題會出現；前一台電腦安裝時明明不會啊? 怎麼這台VM裝起來不一樣!? 總之，這次遇到run Excel VBA時寫不進去SharePoint清單中，問題在.Open時，大概是打不開connection string之類的，之前偶有遇過不同的跟Excel或Access要連SharePoint時相關/類似的問題，大部份都用上述的解決方式就能處理掉，所以在此紀錄一下。

如果我的文章對您有幫助, 您可以隨喜贊助哦~感謝您~