Form bound to ADODB Recordset is read only

[kleky]

I've spent hours on this and I'm perplexed as I've tried everything, yet I can't make edits with ADODB recordset bound to my form.

Access 2010 linking to SQL Server 2008.

Simple form bound to a single table.

Connection string works fine.

Code is as below (cursor etc is set using enums btw). Does anyone have any suggestions?

Many thanks

Private Sub Form_Open(Cancel As Integer)
    Dim rst As ADODB.Recordset
    
    If g1OpenRecordset(rst, "tblName", rrOpenKeyset, rrLockOptimistic, False) = False Then
        Cancel = True
        Exit Sub
    End If
    

    
    Set Me.Recordset = rst

    Set rst = Nothing
    
End Sub

Public Function g1OpenRecordset(ByRef rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, _
                                    Optional rrLock As rrLockType, Optional blnClientSide As Boolean) As Boolean


    If g1Con.State = adStateClosed Then
        g1Con.ConnectionString = g1ConnectionStr
        g1Con.Open
    End If

    Set rs = New ADODB.Recordset
    
    With rs
        .ActiveConnection = g1Con
        
        If blnClientSide Then
            .CursorLocation = adUseClient
        Else
            .CursorLocation = adUseServer
        End If
        
        .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
        .Open strSQL
        
        If .EOF And .BOF Then Exit Function
    End With
    
    g1OpenRecordset = True

    
End Function

Public Function g1ConnectionStr() As String

    g1ConnectionStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ASAM;Data Source=ra_xpp_d37\dev"
    '"Provider=OLEDB;Data Source=RA_XPP_D37\DEV;Database=ASAM;Trusted_Connection=yes"

End Function

 

[kleky]

Balls balls balls!!!

I've just fixed it.

The one simple darn thing I overlooked was setting CursorLocation to Server Side (although I'm sure microsoft said to use client side.)

So...set CursorLocation to client.

 

[DavidAtWork]

Taking a step back, is the database linked to the table in your SQL database, if so try making a simple form with few test fields and have it bound directly to the table using the forms recordsource property and see if you experience the same inability to edit records. If so then it may be a permissions thing with SQL server. I also notice you don't use a userID and password in the connection string, if you did then you can assign write permissions to that user in SQL server

David

 

[kleky]

Thanks David, I did try that and all worked fine. It was a complete mystery; but as I found out, it was due to the cursor setting