Unanswered: Problem updating link between MS Access and SQL Server
I'm trying to create a from a SQL Server 2000 Table to a MSAccess 2000 table. Using DDL, I have created a table in SQL Server but the table does not exist in MS Access Yet. I want to create a linked table in MS Access. This works great. Next I want to link to it.
I use the following code.
.Name = strTableName
Set .ParentCatalog = cat
.Properties("Jet OLEDB:Link Provider String") = strCnn
.Properties("Jet OLEDB:Remote Table Name") = strTableName
.Properties("Jet OLEDB:Create Link") = True
cat.Tables.Append tbl <-- ERROR HAPPENS HERE
The code blows on the Append Line.
Could not find installable ISAM.
The connection string is "Provider=SQLOLEDB; Data Source=DIMENSION4100; Initial Catalog=DIGI; Integrated Security=SSPI; Persist Security Info=False;"
This is the same string that was used to create the table in SQL Server.
Does anyone have any suggestions.
Note the table does not exist in Access when this code is run. Is this causing the problem. Do I first need to create a table in Access and then link it?