Read only tables after linking Access-SQL Server

Read only tables after linking Access-SQL Server

Post by Miche » Fri, 19 Apr 2002 15:55:04



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.

 
 
 

Read only tables after linking Access-SQL Server

Post by Narayana Vyas Kondredd » Sat, 20 Apr 2002 00:01:26


Do you have any primary keys defined on those tables?

--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Quote:> 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.


 
 
 

Read only tables after linking Access-SQL Server

Post by Miche » Sat, 20 Apr 2002 00:28:34


Yes I have primary key on all those tables.
In the meantime, I found the solution.
There is a bug in ADOX, I need to refresh the link just
after appending each table.
So, after DBc.Tables.Append Thistdf, I added :
Thistdf.Properties("Jet OLEDB:Link Provider String") =
ODBCString... and it works.

>-----Original Message-----
>Do you have any primary keys defined on those tables?

>--
>HTH,
>Vyas, MVP (SQL Server)

>http://vyaskn.tripod.com/



>> 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.

>.

 
 
 

1. ADOX : Linking Access and SQL Server leaves Access read only

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.

2. Need: Oracle DBA, Dallas, TX, 6 month, immediate

3. Read Only problems with Linked Tables in Access 97 using SQL 7

4. How much space does numeric data take?

5. SQL Tables are read-only when linked in Access 97

6. Peanut gallery

7. Access + SQL - linked table becomes read-only???

8. SQL Tables are read-only when linked in Access 97

9. Linking Access tables to SQL Server tables

10. Linking SQL Server Table to MS Access Table

11. ADOX.Append Creates a Read-Only Linked Table to SQL Server 7.0

12. HELP!!..accessing access 2000 with linked SQL Server tables