ADOX: "Could not find installable ISAM" error

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.

Krish.

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

Hi Krish

I don't know if was a typo or not, but in your sample call:

LinkATable "Provider=SQLOLEDB.1;Password=%PWD%;User ID=%UID%;Initial
Catalog=%DBNAME%;Data Source=%SERVER%",
"sa","password","mytable","mytable","mydb"

you appear to be passing sConnstrTemplate as the first argument, whereas in
your function declaration:

Public Function LinkATable( _
sUID$, _
sPWD$, _
sSourceTable$, _
sLinkAs$, _
sDBName$, _
sConnstrTemplate$, _
Optional sDSN$ = "", _
Optional sDataSource$ = "", _
Optional sServer$ = "")

it is declared as the sixth argument.

Also, I believe you should have "ODBC;" at the start of your connection
string.


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Krish" wrote in message
...
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.

Krish.

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

Hello Graham,

I called Microsoft today. They told me OLE Provider syntax is not supported
in the connection string.
The following connection string format works to link external table using
the code in my original message:
"ODBC;Driver={SQL
Server};Server=%SERVER%;UID=%UID%;pwd=%PWD%;Databa se=%DBNAME%"

Regards.

Krish


"Graham Mandeno" wrote in message
...
Hi Krish

I don't know if was a typo or not, but in your sample call:

LinkATable "Provider=SQLOLEDB.1;Password=%PWD%;User ID=%UID%;Initial
Catalog=%DBNAME%;Data Source=%SERVER%",
"sa","password","mytable","mytable","mydb"

you appear to be passing sConnstrTemplate as the first argument, whereas
in your function declaration:

Public Function LinkATable( _
sUID$, _
sPWD$, _
sSourceTable$, _
sLinkAs$, _
sDBName$, _
sConnstrTemplate$, _
Optional sDSN$ = "", _
Optional sDataSource$ = "", _
Optional sServer$ = "")

it is declared as the sixth argument.

Also, I believe you should have "ODBC;" at the start of your connection
string.


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Krish" wrote in message
...
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.

Krish.

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