MS ACCESS: Can't edit ado recordset bound to form from an editable query/view

Why arn't you using a server side recordset ?
Why are you cloning it ?

I also don't see the need for a command object.

Just some comments:

As it sounds that you're working on a larger project, you should care about clean code. Thus posting fragments makes not much sense, cause your code should look like this (in a Standard module):

Option Compare Database
Option Explicit

Public Sub FormAssignRecordset(AForm As Access.Form, ASqlStatement As String)

  Dim Connection As ADODB.Connection
  Dim Command As ADODB.Command
  Dim Recordset As ADODB.Recordset

  Connection.ConnectionString = "DSN=<dsnname>"
  Connection.Open
  Connection.ActiveConnection = Connection
  Command.CommandType = adCmdText
  Command.CommandText = ASqlStatement
  Recordset.CursorLocation = adUseClient
  Recordset.CursorType = adOpenDynamic
  Recordset.LockType = adLockOptimistic
  Recordset.Open Command
  Set AForm.Recordset = Recordset
     
End Sub

Select allOpen in new window

And use it in your forms to assign the data source.

While crafting that sample, I stumbled upon this onw: NEVER USE DIM AS NEW!!!

Dim As New works differently in VBA than in VB:

Option Compare Database
Option Explicit

Public Sub TestDimAsNew()
  
  Dim Command As New ADODB.Command
 
  Command.CommandText = "TEST"
  Debug.Print "1 - " & Command.CommandText
  Set Command = Nothing
  Debug.Print "2 - " & Command.CommandText
  
End Sub

Select allOpen in new window

The second case should raise an exception, but it doesn't. Cause Dim As New creates a new instance automatically when needed.

First of all, your recordset with the query above is NOT UPDATABLE!
It is not enough to set the IsUpdateable property to True. The query cannot contain a join.

@Máté: Queries in SQL Server are normally updatable as long as you change only one base table at a time.

       .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open com

 One of the big things to understand here is that what you ask for is not always what you get.  The JET OLEDB provider has restrictions on what it can supply.   Below is a recap.   Note that CusorType Dynamic is not supported at all and you will always get a Keyset cursor. Likewise, if you request a lock type of adLockOptimistic, adLockBatchOptimistic, or adLockPessimistic, you will also get a Keyset cursor.

The other big thing is to make sure you have a primary key, and that it is included in the recordset.  This is required for a Keyset cursor, which is what you almost always get.

Jim.

' ADO Cursor types (lighest to heavyest)
' adOpenUnspecified -1 Unspecified type of cursor
' adOpenForwardOnly - 0 A forward-only cursor. Cannot move backward.  If you do, recordset is closed and source requeried.
' adOpenStatic - 3 A static copy of a set of records. Additions, changes, or deletions by other users are not visible.
' adOpenKeyset - 1 Static snapshot of PK values.  As you scroll through records, records are fetched.  You can see updates made by other users, but not new records added.
' adOpenDynamic - 2 All changes by other users are visible.  This cursor type however is not supported by the JET OLEDB provider.

' Lock types
' adLockUnspecified - -1 Unspecified type of lock. Clones inherits lock type from the original Recordset.
' adLockReadOnly - 1 Default. Read-only records
' adLockPessimistic - 2 Pessimistic locking, record by record. The provider lock records immediately after editing
' adLockOptimistic - 3 Optimistic locking, record by record. The provider lock records only when calling update
' adLockBatchOptimistic - 4 Optimistic batch updates. Required for batch update mode

' Resulting cursor type based on type requested and lock type for JET OLE DB Provider:
' adOpenForwardOnly/adLockReadOnly - Forwardonly
' adOpenForwardOnly/adLockPessimistic - Keyset
' adOpenForwardOnly/adLockOptimistic - Keyset
' adOpenForwardOnly/adLockBatchOptimistic - Keyset

' adOpenStatic/adLockReadOnly - Static
' adOpenStatic/adLockPessimistic - Keyset
' adOpenStatic/adLockOptimistic - Keyset
' adOpenStatic/adLockBatchOptimistic - Keyset

' adOpenKeyset/adLockReadOnly - Keyset
' adOpenKeyset/adLockPessimistic - Keyset
' adOpenKeyset/adLockOptimistic - Keyset
' adOpenKeyset/adLockBatchOptimistic - Keyset

' adOpenDynamic/adLockReadOnly - Keyset
' adOpenDynamic/adLockPessimistic - Keyset
' adOpenDynamic/adLockOptimistic - Keyset
' adOpenDynamic/adLockBatchOptimistic - Keyset

' NOTE TO ABOVE:
'  Command type of adCmdTableDirect always yields a Keyset cursor.
'  Command type of adCmdText and adCmdTable yield types above
'  Requesting a client side cursor always gives you a Static cursor.

This wasn't really a snippet of code it is just a smaller test function Private Sub Form_Open(Cancel As Integer) from a test form i'm using to troubleshoot this problem.  That's also why the variable names aren't the most clear as they don't need to be in this case.  I'll go ahead and paste a slightly updated version of the code based on some notes made here that still doesn't work.  Let me address some of the comments so far first:

Fabrice
-Why am i using client cursor? - As i understand it a server side cursor to SQL is always uneditable.
-Why did i clone? - I was testing various option for leaving certain things open/closed like the connection and the recordset.  Using a clone would let me do rs.close and keep the data i think so long as i don't set rs = Nothing.  I should be able to close the connection too i think, but I've tried it any number of ways in frustration.
-Why am i using command? - Because the sql server is using application roles for security and i have to run the sp_setapprole command first.  I've tested this in an unsecured version of the database that doesn't require the elevation as it's an admin account, so I left that part out of the code because it still isn't working even like that.  Either way i need to use command so I can execute the sp_setapprole statement so the connection has the proper permissions.  (I didn't wan't to confuse the issue since i've ruled it out as part of the problem by testing in this manner.)  I did try using command just for that elevation and then rs.source for the SQL but i kept crashing and getting weird errors.  Maybe I'll try it again with the other changes.

step5an
-Dim As New - I took this out of the code and instantiated the objects in module (e.g. set con = New ADODB.connection), but I'm still having the same issue.

Jim
-I'm aware that there are nuances with the cursor/lock type, but I am new at this so i kind of tried all the combinations.  The one i posted was just the last thing I had tested.  Thank you for the great breakdown though I'll save that for future reference.

-I'm thinking the PK fields MAY possibly have something to do with this.  I know that when linking to one of my SQL views you have to TELL access what the key fields are (since views don't inherently have them just like a query doesn't...they're effectively inherited from the source tables).  Is there a way to tell Access what the key fields are when pulling a recordset off a view in VBA?

-I will note, however, that i did test the recordset directly and i am able to change a value in the current record then call the .Update procedure and it does change the server value so the recordset still appears to be editable so there's some disconnect between this and the form maybe?

Here is a slightly updated version of my code that still isn't working given the comments.  (To be clear i'm testing this on the admin account that isn't requiring the sp_setapprole command to be run, but i'm still using command since i will need to run it.):

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    Dim con As ADODB.Connection
    Dim com As ADODB.Command
    Dim rs As ADODB.Recordset

    Set con = New ADODB.Connection
    Set com = New ADODB.Command
    Set rs = New ADODB.Recordset
   
    con.ConnectionString = "DSN=<dsnname>"
    con.Open
    com.ActiveConnection = con
    com.CommandType = adCmdText
   
    com.CommandText = "SELECT * FROM dbo.vw_AHE_Override"
   
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open com
    End With
   
    Set Me.Recordset = rs
   
    rs.Close
    con.Close
End Sub

UPDATE: So i got this SOMEWHAT working.  I did end up having to use rs.Source rather than the command for the SQL and i can now edit the recordset.  I realize however why this wasn't working for me when I first tried it.  The returned data set was chugging too long and it timed out.  Here's the strange thing.  To get this to work I had to filter it down to around a thousand records.  The SQL view will show over 100k records in under 1 second so I know the view code isn't slow.  Why is the recordset pull from ADO so slow?  The source data does start from a very large number of records, but it's limited to the last few weeks of data so it never pulls more than ~100k records (unfiltered) which isn't really that much.

Is this some limitation with an ADO recordset, because if so I'm going to have big problems as I have other place in my data where there really are 100k results (filtered).  I know it may sound like a bad database result to show that much, but when you consider it's out of 5+million records it's really not.

1) It takes time to transmit 100k rows. Even the first row is returned very quickly.
2) No user will ever read 100k rows. This is a bad concept. Limit the result to max. 1000 rows. Any logic requiring that much data should be run as stored procedure on the server.

This is a major construction project.  These planners will enter over 1000 records in a clip on a slow day and they need to see all the steps so they know which ones to progress.  They can filter them down further on the form if they want to to see less, but the default state shows everything because the construction coordinators need to be able to see the progress of different sections of work.  I will say that this database design has been working for over 2 years now but we've exceeded the size limits of access in a few individual tables now (or close to it),  so we're moving to SQL.  Of course, i don't want to have to explain the merits of the entire database in detail, but why then did the recordset display almost instantly when it was uneditable and the SQL was a command that opened, but when i set it as a source to the recordset it wont open that amount of data.  Even just opening that 1100 rows takes over 15 seconds.

All of the logic that is pulling the data IS being run on the server...Access just needs to receive and display the data.  Previously with linked tables/views there were one or two specilized items that had an access calculated component because it doesn't work in SQL, but i'll be rethinking those processes completely.   I realize it takes time to display the recordset, but I had some forms that pulled over 300k records (minor filter only) before with just link tables and they loaded instantly because I think it displays the first rows as they're pulling while still calculating the rest of the rows.  If i were to jump to the last record i'd have to wait a couple of seconds but it would still load in under 5.  Heck, i pulled the full 5 million record table just to see at one point and it took about 17 seconds to load completely in the form.  It shouldn't take that same amount of time to load 1100 records.  Something's amiss here.

Ok so I've been plugging away at this and have had some very odd behavior.  Here is a copy of the updated code that will let me edit the form, but it's running slowly.   The additional WHERE criteria are just to get the recordset down to 1188 records so it will load and not crash (takes about 15-20 seconds to load like this).  At the very least it's editable like this.

Now in the below instance I'm not doing anything with com (because i don't need to execute the app role on this account) so to test i just commented it out.  It loads instantly again, BUT now once again...i can't edit it.  The only correlation in the code below between the recordset and the command is that they have the same active connection.  I also tried adding in a Set com = Nothing before the recordset steps (because I could do this once the connection is elevated).  Again it opens instantly, but can't edit.  Can someone explain what in the blazes is going on here?

Private Sub Form_Open(Cancel As Integer)
    Dim con As ADODB.Connection
    Dim com As ADODB.Command
    Dim rs As ADODB.Recordset

    Set con = New ADODB.Connection
    Set com = New ADODB.Command
    Set rs = New ADODB.Recordset
   
    con.ConnectionString = "DSN=PCN Backend SQL"
    con.Open
    com.ActiveConnection = con
    com.CommandType = adCmdText
   
    With rs
        .ActiveConnection = con
        .Source = "SELECT * FROM dbo.vw_AHE_Override WHERE d_Timesheet_Date > '11/19/2018' AND i_Project_ID=3 AND i_Craft_ID=36"
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With
   
    Set Me.Recordset = rs
   
    rs.Close
    con.Close
End Sub

You can try to remove the

rs.Close
con.Close

Select allOpen in new window

Then work with your data and on the form close...close the con object.
It has being some years since i have seen and used this...it was from an Article on Linked i think...stating that leaving the ADO recordset open works...the bad thing is that i have lost it...

Unless I'm wrong, server side recordset need to be opened with AdOpenStatic cursorType and AdLockOptimistic lockType.

Other solution involve declaring your connection and recordset variables at form level.
For now, they are declared in the event Handler (locally), and they're automatically destroyed when the event Handler is done.

Sample code:

Option Explicit
Private cn As Object    '// ADODB.Connection
Private rs As Object    '// ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = "DSN=PCN Backend SQL"
    cn.Open

        '// recordset can be opened with default cursor and lock (thus will remain server side)
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM dbo.vw_AHE_Override", cn
        '// ect ...
End Sub

Private Sub Form_Close
        '// optional but won't hurt
    rs.Close
    cn.Close
End Sub

Select allOpen in new window