Creating an updatable query recordset using Access 2010

[Bill McCoy]

I am trying to create a recordset in Access 2010 using a query. The query uses global variable that captures the users log-name to filter for the records that they entered. The query executes, but the records are not updatable.

The code uses ADO. My goal is to split this database to create a multi-user environment. The source table has a primary key and there are no calculated fields.

Below is the code. Can someone please explain how I can make this recordset updatable.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class,
'and set its properties


Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM FACT_PROGRESS_NOTES WHERE [USER_NAME] = '" _
& strUser & "'"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

Thanks,

Bill

[June7]

Why are you using code to set a connection and why code to set form's RecordSet?

Will the backend of split db be Access file? Set links to the backend tables with UNC pathing. No code needed to set connection. Set form's RecordSource property with the SQL statement.

Otherwise, with code, consider:
.LockType = adLockPessimistic
.CursorType = adOpenDynamic