While testing a question asked here (see Display a field from a Access database), I found something mysterious. Please read on!

The guy had problem updating a recordset returned from a command. I am pretty it is caused by the LockType property. He is using the DataEnvironment and since I really hate the DataEnvironment, I had reproduce it by code. What a surprise, I can't update! Is it me (I really hope so) or is it a bug (read feature) implemented by our great friends at Microsoft?

Here is the code that I have used to test (to help you correct me):

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim com As ADODB.Command
Dim rst As ADODB.Recordset

'Open the connection
Set cn = New ADODB.Connection
With cn
' .ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Projets VB\Tips & Techniques\ADO Command\db1.mdb;Persist Security Info=False"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Projets VB\Tips & Techniques\ADO Command\db1.mdb;Persist Security Info=False"
' .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Table1;Data Source=MOREAU"
.Mode = adModeShareDenyNone
.Open
End With

'Create the command
Set com = New ADODB.Command
With com
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "SELECT * FROM Table1"
Set rst = .Execute
End With

' 'Open the recordset
' Set rst = New ADODB.Recordset
' With rst
'' Set .ActiveConnection = cn
' .CursorType = adOpenKeyset
' .LockType = adLockOptimistic
' .CursorLocation = adUseClient
'' .Open ("SELECT * FROM Table1")
' Set rst = com.Execute
'' MsgBox .RecordCount
' End With

'Update recordset
With rst
Do Until .EOF
!Field2 = "New value"
.Update
.MoveNext
Loop
End With

'Close the recordset
rst.Close
Set rst = Nothing

'Close the command
Set com = Nothing

'Close the connection
cn.Close
Set cn = Nothing
End Sub


As you see in this example, I have tried a couple of things. Please let me know if you succeed or not.