OLEDB connection to Access database with password: “Could not find installable ISAM”

Question

Problem: I get the following error when trying to communicate with an MS Access database via MS Excel: "Could not find installable ISAM".

Excel File Extension = .xlsm
Access File Extension = .mdb
MS Office Version = Office 2013
Operating System = Windows 7 Home Premium (64 Bit)

Troubleshooting Attempts Made:

1. Microsoft Support Page: https://support.microsoft.com/en-us/kb/209805
2. StackOverflow Page A: Troubleshooting Could not find installable ISAM error
3. StackOverflow Page B: How to resolve "Could not find installable ISAM." error for OLE DB provider "Microsoft.ACE.OLEDB.12.0"
4. StackOverflow Page C: Could not find installable ISAM when importing Access table to Excel

Here is the VBA code:

    Dim cnn as New ADODB.Connection
    cnn.Provider = "Microsoft.ACE.OLEDB.12.0;"
    cnn.ConnectionString = "Data Source=" & Range("fld") & "\MyDB.mdb:Jet OLEDB: Database Password=" & "Range("pwdDB")
    '≈Do stuff
    cnn.close
    set cnn = nothing

Does anybody know how to solve this?

Answer 1

Thanks Everyone.

I found out what it was: Leading Equals Sign in the Password (For a protected Access Database).

► Issue: The connection string throws the "Installable ISAM" error if the database password starts with an equal sign ("=").
► Solution: Remove the leading equals sign in the password.

The error message really doesn't detail that, or hint that the password is the problem.

I sure hope this helps someone else in the future.

Answer 2

Further to the other answer, we can in fact handle passwords that begin with an equals sign if we enclose the password in double quotes. For example if the password is =test, we use

Jet OLEDB:Database Password="=test"

and if the password contains double-quotes then we need to double them up (as usual for double quotes inside a double-quoted string literal), so for the password ="test we need to use

Jet OLEDB:Database Password="=""test"

VBA code for the general case, assuming that the password is in a String variable named pwd:

Dim connStr As String
connStr = "Data Source=C:\__tmp\pwdTest.accdb;"
If Len(pwd) > 0 Then
    connStr = connStr & "Jet OLEDB:Database Password=""" & Replace(pwd, """", """""") & """;"
End If
Dim cnn As New ADODB.Connection
cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.Open connStr
Debug.Print "Connected using '" & connStr & "'"