ADOX: Jet Error -2147467259 (80004005) Could Not Find Installable ISAM

jujiro_eb · June 8th, 2009, 10:15 PM

Folks,

I have used the following code to link SQL Server tables in MSAccess using
ADO connection string. On my newly built XP machine I am getting this
dreadful error -2147467259 (80004005) Could not find installable ISAM.
OS: Windows XP Svc PK 3
ADO version 2.8

Any help will be highly appreciated.

Ash.

Code:

LinkATable "Provider=SQLOLEDB.1;Password=%PWD%;User ID=%UID%;Initial 
Catalog=%DBNAME%;Data Source=%SERVER%", 
"sa","password","mytable","mytable","mydb"
 
Public Function LinkATable( _
    sUID$, _
    sPWD$, _
    sSourceTable$, _
    sLinkAs$, _
    sDBName$, _
    sConnstrTemplate$, _
    Optional sDSN$ = "", _
    Optional sDataSource$ = "", _
    Optional sServer$ = "")
 
    Dim oCat As Object
    Dim oTable As Object
    Dim sConnString$
 
    sConnString = sConnstrTemplate
    sConnString = Replace(sConnString, "%DSN%", sDSN)
    sConnString = Replace(sConnString, "%UID%", sUID)
    sConnString = Replace(sConnString, "%PWD%", sPWD)
    sConnString = Replace(sConnString, "%DBNAME%", sDBName)
    sConnString = Replace(sConnString, "%SERVER%", sServer)
 
    ' Create and open an ADOX connection to Access database
    Set oCat = CreateObject("ADOX.Catalog")
    Set oCat.ActiveConnection = CurrentProject.Connection
 
    ' Create a new Table object
    Set oTable = CreateObject("ADOX.Table")
    With oTable
        .Name = sLinkAs
        Set .ParentCatalog = oCat
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Remote Table Name") = sSourceTable
        .Properties("Jet OLEDB:Link Provider String") = sConnString
        .Properties("Jet OLEDB:Cache Link Name/Password") = True
        .Properties("Jet OLEDB:Link Datasource") = sDataSource
    End With
 
    ' Add Table object to database
    oCat.Tables.Append oTable ' GIVES THE ERROR
    oCat.Tables.Refresh
    Set oCat = Nothing
 
End Function

jujiro_eb · June 9th, 2009, 01:45 PM

After digging through and experimenting, I realize that linking with OLE DB providers is not possible. ADOX recognizes only certain type of connect strings. My intent was to use a DSN less connect string. The connect string format which works to link external SQL Server tables, is as following:

Code:

strConnstr=
"ODBC;Driver={SQL Server};Server=%SERVER%;UID=%UID%;pwd=%PWD%;Database=%DBNAME%"

mmcdonal · June 9th, 2009, 01:50 PM

I found this article at MS: http://support.microsoft.com/kb/209805

I would reinstall MDAC as well. http://msdn.microsoft.com/en-us/data/aa937730.aspx

Did that help?