Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Current Recordset does Not Support Updating

    Below you will see function from a form where I have a multi-select list box "permissionlist." The attempt here is to update the table dbo_tblPermissions so that all of the available permissions are in the table for the user - adding if necessary to the table, and setting them all to have dbo_tblPermissions!Authorized set to false. Then to go back and set all of the .inselected items to true. This may not be the best way to do this, and I am certainly open to suggestions (originally I had thought only to have the "selected" items existing in the table, but this seems a little cleaner to prevent deleting and adding a bunch of times.

    In any case, when I click on the button cmdUpdatePermissions, I get the message "Run Time Error '3251': Current Recordset does not support updating. This may be a limitation of the provider, or the selected locktype."
    This table is on SQL Server via the ODBC connection. Office Access 2007, in Accdb file format. The code is below. Any ideas?

    Private Sub cmdUpdatePermissions_Click()
    Dim query As String
    Dim varItm As Variant
    Dim ctl As Control
    Dim rs2 As New ADODB.Recordset

    Set ctl = Me.PermissionList
    query = "select * from dbo_tblPermissions"


    rs2.Open query, CurrentProject.Connection, adOpenDynamic

    'clear current permissions and add permissions to list if necessary
    For Each varItm In ctl.ItemsSelected
    rs2.Find ("[permissionname]='" & ctl.ItemData(varItm) & "' and [userid]=" & Me.PermUserID)
    If rs2.EOF Then
    rs2.AddNew
    rs2.Fields!UserID = Me.PermUserID
    rs2.Fields!permissionname = ctl.ItemData(varItm)
    rs2.Fields!Authorized = False
    Else
    rs2.Fields!Authorized = False
    End If
    Next varItm

    'Set the currently selected items as authorized in the table
    For Each varItm In ctl.ItemsSelected
    rs2.Find ("[permissionname]='" & ctl.ItemData(varItm) & "'")
    rs2.Fields!Authorized = True
    Next varItm

    rs1.Close
    rs2.Clone
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    did you define a primary key on tblPermissions? Do you have UPDATE permissions on the remote resource?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2003
    Posts
    103
    Oops, nevermind. I needed to set the Lock option.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •