General information
Category:
Database DAO/RDO/ODBC/ADO
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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only