none
OpenRecordset Errors with Object is Read-Only if User Doesn't Have Write Permissions RRS feed

  • Question

  • I have an Access 2010 database with SharePoint linked tables.  Where the code creates a recordset on a linked table and the end user has Read-Only permission to the table (SharePoint linked list), an error message is returned with 'object is read-only' at the line of code below.  If the user has write/contribute permission to the table, the recordset is created without error.

    Set rs = Db.OpenRecordset(strSql, dbOpenDynaset, dbReadOnly)
    How can I create a recordset in read-only mode regardless of the end users permission level for the table (provided they have some level of permission)?

    Wm. James

    Friday, August 14, 2015 4:37 PM

All replies

  • Hi. Try replacing dbOpenDynaset with dbSnapShot. Hope that helps...
    Friday, August 14, 2015 4:54 PM
  • I tried that one as well and got the same results.

    Set rs = Db.OpenRecordset(strSql, dbOpenSnapShot)


    Wm. James

    Friday, August 14, 2015 6:01 PM
  • For context, the following is the full section of code, which refreshes the SharePoint list links:

        For Each tbl In Db.TableDefs
            If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
                If Left(tbl.Name, 8) <> "UserInfo" Then
                    If Left(tbl.Connect, 3) = "WSS" Then
                        strSql = "SELECT * FROM [" & tbl.Name & "];"
                        Set rs = Db.OpenRecordset(strSql, dbOpenDynaset, dbReadOnly)
                        If Not rs.Updatable Then
                            DoCmd.SelectObject acTable, tbl.Name, True
                            DoCmd.RunCommand acCmdRefreshSharePointList
                        End If
                    End If
                End If
            End If
        Next
    The line of code that throws the error is:
    Set rs = Db.OpenRecordset(strSql, dbOpenDynaset, dbReadOnly)
    Which has also been tried as the following, with the same result:
    Set rs = Db.OpenRecordset(strSql, dbOpenSnapShot)

    Wm. James

    Friday, August 14, 2015 6:17 PM
  • Hi. Thanks for the additional information. Does your code work for those with appropriate permission to the List? I tried your code, and I got a different error to a list that I have full permission. I got a "could not lock table" error (3262).
    Friday, August 14, 2015 6:26 PM
  • For users that have at least write/contribute permission for the SharePoint list(s), the code runs without error.

    Wm. James

    Friday, August 14, 2015 6:28 PM
  • Okay. I'm not sure why you would get a different error then. Have you tried stepping through the code to make sure the refresh link is getting executed for users with contribute permissions? In my SP environment, the connect string starts with "ACEWSS," so I'm not sure I can troubleshoot this properly for you. Sorry...
    Friday, August 14, 2015 6:36 PM
  • Thanks for the help.  I just stepped through the code again and everything is being executed as expected, where I am the user (full permissions).

    I pulled the tbl.Connect value and after the If Left(tbl.Connect, 3) = "WSS", the tbl.Connect returns the following value:
    WSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=[webURL here];LIST=[listID here];VIEW=;RetrieveIds=Yes


    Wm. James

    Friday, August 14, 2015 7:08 PM
  • I'm not sure if this is the issue, but the way the SharePoint list permissions are set, the end users have "Limited Access" to the list and "Contribute" or "Read" for specific items in the list.  I'm thinking that, the 'Object is Read-Only' error is because the end users don't explicitly have at least "Read" permission to the entire list.

    Wm. James

    Friday, August 14, 2015 7:45 PM
  • Hi. Not sure what else to recommend since we have different environments. However, if I understand your intention correctly, why do you need to loop through all the table and refresh "all" the links each time? Also, your If statement basically says "if recordset is not updatable, then refresh the links." What is the purpose of that? If I read the code correctly, you're opening the recordset as "read-only" which means it won't be updatable for any user, so that means you basically want to refresh all the links each time the code runs. If that's the case, why even check for the recordset? Why not just execute the RunCommand to refresh the link without all the other stuff? Just curious...
    Friday, August 14, 2015 7:45 PM
  • After stepping through that section of code again, I've "cleaned" it up a bit.  The original code was taken from recommendations from other forums.

        For Each tbl In Db.TableDefs
            If InStr(tbl.Connect, "WSS") = 1 And InStr(tbl.Connect, "UserInfo") = 0 Then
                tbl.RefreshLink
            End If
        Next

    All of that being done, I've also realized that for what I am doing, I don't need to initiate a refresh link.  However, this was just the first place that the "Read-Only" error showed up.  I'm seeing it at every OpenRecordset, of a SharePoint linked list, for the end users.

    Thanks again for all of your help.


    Wm. James

    Friday, August 14, 2015 8:00 PM
  • Hi. Glad to hear you're making progress. If the read-only users can view the data by manually opening the table, then I would think an OpenRecordset code should work for them too as long as you try to open it as non-updatable. So, I'm not sure what else to recommend for you to try. I would just use dbOpenSnapshot instead of dbOpenDynaset.

    Oh wait, perhaps one other thing you could try is ADO Open method. Maybe you'll have better luck with that. Cheers!

    Friday, August 14, 2015 8:13 PM
  • I finally got to sit with an end user for an extended period of time (although not enough) and step through the code from their vantage point.  Looping through all of the lists and opening them with the same code revealed that the end user can open some lists but not all.

    strSql = "SELECT * FROM [" & lst & "]"
    Set rs = Db.OpenRecordset(strSql, dbOpenSnapshot)

    What is odd, is that the user has the same "Limited Access" permission in all of the SharePoint lists.  The frustration level is mounting, but I will prevail.


    Wm. James

    Friday, August 14, 2015 9:29 PM
  • Hi. Not sure if you're saying the same thing but limited access could mean that the user can only read specific list items and not the entire list. Just a thought...
    Saturday, August 15, 2015 12:50 AM
  • I've narrowed it down to a single linked list (1 of 22) that is being returned as Read-Only.  The odd part is that the end user has the same permission level on that linked list as the other linked lists and the lists are constructed with very nearly the same data structure

    Other than the end user not having appropriate permissions, what are the other cases that OpenRecordset would be returned as Read-Only?


    Wm. James

    Monday, August 17, 2015 7:43 PM
  • Here is something else that is odd, the error message is 3027: "Cannot update. Database or object is read-only."  I'm not trying to update anything, just read the data.

    strSql = "SELECT [Assigned],[MRC 1-2],[MRC 3A-3B],[MRC 4],[MRC Percent],[Max MRC Percent] " _
    "FROM [Medical Readiness] " _
    "WHERE [Organization]=241 AND [FYQTR]='FY15-QTR3'"

    Set rs = Db.OpenRecordset(strSql, dbOpenSnapshot)

    And, the code runs fine for users with full permissions.  It only throws this error for end users who have Read only permission and only on this one list.


    Wm. James

    Monday, August 17, 2015 9:19 PM
  • Solved...kind of...at least the immediate issue.

    I changed one end user's permissions on one list item from 'Read' to 'Full Control' and the code worked (expected).

    I then changed the same user's permissions down to 'Design' and the code worked (expected).

    I then changed the same user's permissions down to 'Read' and the code worked (desired but unexpected).  When the permissions were set to 'Read' previously, the code did not work. Not only did it fix the issue for this one user, but all users.

    In all of my years working with MS Access (FE) and SharePoint (BE), I've never encountered this issue and I'm still unsure what just happened.  I will be adding some error handling just for this error.

    The questions I have now are:

    What happened to the table to cause the error to occur in the first place?

    Why did the actions I took fix the issue?


    Wm. James

    Friday, August 21, 2015 1:47 PM