PRB: ADOX Creates Read Only Linked Tables to Access Databases (276035)

MORE INFORMATION

Running the Refresh method on the tables collection (cat.Tables.Refresh) does update the table objects in the collection but does not refresh the link to the database.

The following example assumes that you are familiar with ActiveX Data Objects (ADO) and ADOX technologies. The example links a SQL Server table to a Microsoft Access database. You may want to change the server argument in the connection string to the name of your SQL Server server.

Steps to Reproduce Behavior

1. Open a new Microsoft Visual Basic Standard EXE project and set a reference to:
   
   
   • The Microsoft ActiveX Data Objects 2.x Library.
     
     -and-
     
     
   • Microsoft ADO Ext. 2.x for DDL and Security.
2. Add two command buttons to Form1 (the default form), and then set the Name property to cmdLinkTable and cmdDeleteLinkedTable.
3. Add two option buttons to Form1, and then set the Name property to optNoRefresh and optRefresh.
4. Set the Value property to True for the optNoRefresh option button.
5. Copy, and then paste the following code into Form1:
   
   Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table
   Dim cnAccess As New ADODB.Connection
   
   Private Sub cmdDeleteLinkedTable_Click()
   
       cat.Tables.Delete "lnkAuthors"
       MsgBox "Link Deleted"
   
   End Sub
   
   Private Sub Form_Load()
   
       cmdDeleteLinkedTable.Caption = "Delete Linked Table"
       cmdLinkTable.Caption = "Link Table"
       optNoRefresh.Caption = "Don't Refresh Link"
       optRefresh.Caption = "Refresh Link"
       
       cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;"
       cat.ActiveConnection = cnAccess
       
   End Sub
   
   Private Sub Form_Unload(Cancel As Integer)
   
       'Clean up code
       Set cat = Nothing
   
       cnAccess.Close
       Set cnAccess = Nothing
       
   End Sub
   
   Private Sub cmdLinkTable_Click()
      
       Dim rs As New ADODB.Recordset
       
       tbl.Name = "lnkAuthors"
       Set tbl.ParentCatalog = cat
   
       tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=<username>;pwd=<strong password>;"
       tbl.Properties("Jet OLEDB:Remote Table Name") = "Authors"
       tbl.Properties("Jet OLEDB:Create Link") = True
   
       cat.Tables.Append tbl
       
       If optNoRefresh.Value = False Then
           'Executing the following line will refresh the link and the table will be Updateable.
           tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=<username>;pwd=<strong password>;"
       End If
       
       rs.CursorLocation = adUseClient
       rs.Open "Select * from lnkAuthors", cnAccess, adOpenStatic, adLockOptimistic
       
       rs!State = "WA"
       rs.Update 'The error occurs here if the link is not refreshed.
       
       MsgBox "Recordset updated"
       
       rs.Close
       Set rs = Nothing
       
   End Sub
   					

6. Run the project and make sure that you select the option button with the caption Don't Refresh Link. Click Link Table and note the error. Stop the project execution.
7. Re-run the project, and click Delete Linked Table to delete the linked table created in the previous step.
8. Select the option button with the caption Refresh Link, and then click Link Table. Note that no error occurs and that the message "Recordset updated" is displayed.