Eric,

I had ran into a similar problem. See MS KB Article Q188857; hopefully
that will help. The problem still exists in ADO as far as I know.


HTH,
Ed


>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.