ADOX : Linking Access and SQL Server leaves Access read only

ADOX : Linking Access and SQL Server leaves Access read only

Post by Miche » Fri, 19 Apr 2002 23:15:23



I want to link SQL Server tables to an Access mdb.
Everything is OK when I link thru Access Get External Data
function using a DSN.
When I do this thru ADOX, all my tables are "read only"
(even when opening in Access).
How can I solve this problem.
Here my code :

Set db = New ADOX.Catalog              
db.ActiveConnection = "Provider=SQLOLEDB;Data
Source=Server & ";Initial Catalog=" & f & ";User
Id=sa;Password=;"
ODBCString = "ODBC;DSN=eBooks"
For Each tdf In db.Tables
  If tdf.Type = "TABLE" _
    Set Thistdf = New ADOX.Table
    With Thistdf
    .ParentCatalog = DBc
    .Name = tdf.Name
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Link Provider String") =
ODBCString
    .Properties("Jet OLEDB:Remote Table Name") = tdf.Name
    End With
    DBc.Tables.Append Thistdf
    Set Thistdf = Nothing
  End If
Next tdf

Nota : I tried with a lot of options (trusted connection,
storing password, aso) without any success.