Microsoft KB Archive/276035

= PRB: ADOX Creates Read Only Linked Tables to Access Databases =

Article ID: 276035

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5

-



This article was previously published under Q276035



SYMPTOMS
Programmatically linking an external table to a Microsoft Access database may cause the linked table to have a Read Only status, and the following error message occurs when you try to update the table:

Run-time error '-2147467259 (80004005)':

operation must use an updateable query



CAUSE
This error message may occur because:
 * The source table does not have a primary key.

-or-


 * You use Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) to create the link without first refreshing the link.



RESOLUTION
To resolve this problem, make sure that the source table has a primary key, and then apply one of the following approaches:
 * In Microsoft Access, refresh the link. To refresh the link, from the Tools menu, point to Database Utilities and then click Linked Table Manager. In the Linked Table Manager dialog box, select the tables that you need to refresh, and then click OK.


 * If you are linking new tables by using ADOX, make sure that you refresh the link by setting the Jet OLEDB:Link Provider String property in code again after appending the table to the tables collection as demonstrated in the &quot;More Information&quot; section.



STATUS
This behavior is by design.



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
 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.  Add two command buttons to Form1 (the default form), and then set the Name property to cmdLinkTable and cmdDeleteLinkedTable.</li> Add two option buttons to Form1, and then set the Name property to optNoRefresh and optRefresh.</li> Set the Value property to True for the optNoRefresh option button.</li>  Copy, and then paste the following code into Form1:

Note You must change User ID= and 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 &quot;lnkAuthors&quot; MsgBox &quot;Link Deleted&quot;

End Sub

Private Sub Form_Load

cmdDeleteLinkedTable.Caption = &quot;Delete Linked Table&quot; cmdLinkTable.Caption = &quot;Link Table&quot; optNoRefresh.Caption = &quot;Don't Refresh Link&quot; optRefresh.Caption = &quot;Refresh Link&quot; cnAccess.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;&quot; 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 = &quot;lnkAuthors&quot; Set tbl.ParentCatalog = cat

tbl.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;ODBC;Driver=SQL Server;Server= ;database=pubs;uid= ;pwd= ;&quot; tbl.Properties(&quot;Jet OLEDB:Remote Table Name&quot;) = &quot;Authors&quot; tbl.Properties(&quot;Jet OLEDB:Create Link&quot;) = 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(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;ODBC;Driver=SQL Server;Server= ;database=pubs;uid= ;pwd= ;&quot; End If   rs.CursorLocation = adUseClient rs.Open &quot;Select * from lnkAuthors&quot;, cnAccess, adOpenStatic, adLockOptimistic rs!State = &quot;WA&quot; rs.Update 'The error occurs here if the link is not refreshed. MsgBox &quot;Recordset updated&quot; rs.Close Set rs = Nothing End Sub </li> 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.</li> Re-run the project, and click Delete Linked Table to delete the linked table created in the previous step.</li> Select the option button with the caption Refresh Link, and then click Link Table. Note that no error occurs and that the message &quot;Recordset updated&quot; is displayed.</li></ol>

Keywords: kbcodesnippet kbprb kbmdacnosweep KB276035

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.