Friday, October 5, 2012

MS Access SharePoint Read Only Linked Library

Scenario
  • You are getting external data from a SharePoint library with Microsoft Access and wish to edit, insert, update, or delete the records
  • The library requires approval of submitted documents
Issue
  • You receive any of the following errors due to the list/library being read-only
    • "Cannot update. Database or object is read-only."
    • "This Recordset is not updateable."
    • etc.
  • You may also receive the "The setting you entered isn't valid for this property." 
    • You can ignore this one as it is irrelevant to this issue and will not prevent updating the list/library.
Resolution
  • Disable the approval requirement in the document library versioning settings. Re-enable when done.
    • This seems to auto-approve any that are in progress. 
    • In addition, the Approval Status column may disappear from configured views.
  • Side note: For records with a populated person/group column, MS Access may hang during an update (via query or linked list manual update).  Removing and re-adding the users cleared this up for me.  And yes, this was strange.
  • The following scenario also does not affect the read-only status:
    • The list or library you are accessing included a managed metadata field at some point in time

      You can verify this by viewing the linked library in Design View in MSAccess and looking for a column named "Taxonomy Catch All Column". If it exists, then this list had a Term Store (Managed Metadata) column associated with it. Ex: Enterprise Keywords

5 comments:

  1. This is very useful information, thanks. I've noticed a strange problem I have with a MS Access database linked to some Office 365 Lists. If I open the database from Office 365 by default it opens as read-only. In this state when I try to update some records in one of the lists it simply hangs and crashes MS Access. If I open the database as read-write then it always works. I can replicate this with an Access database linked to the lists stored on my C: drive. When read-only it hangs on updating. What is particularly strange is if I look at the design of one of the lists the "Created By" and "Modified By" fields show as being Text type with a length of 255 characters when the database is opened read-write. When it's opened read-only the field types show as number. Could it be that this is causing the problem perhaps? I'm going to try and get this logged somewhere else but your thoughts would be much appreciated.

    ReplyDelete
  2. Thanks for the post. My list does had a Metadata field which I removed. When I open the list in Design mode in Access, it shows the field "Taxonomy Catch All Column". Is there any way I can remove this field?

    ReplyDelete
  3. lookup columns are not added.
    while inserting into sharepoint lists which contains lookups it throghs msg: "Cannot update. Database or object is read-only."

    ReplyDelete
  4. My Access 2010 freezes when linked to SharePoint 2013 on certain records. I cannot find anything wrong with them (I have checked all lookup values and formats) and I get no errors? I did not have this problem until a week or so ago when our IT upgraded SharePoint from 2010 to 2013. Some records let me update and the ones that did not I deleted from SharePoint and entered new ones - now the new ones I entered are the ones I cannot access. I know this is an old post, but I am at my wits end!!!!!

    ReplyDelete
  5. so it is not possible to update metadata for managed metadata fields in a SharePoint document library via MS Access VBA script ?

    ReplyDelete