ADO record sets. Does anybody know whats wrong with this code?

[NigelRaper]

When i execute the following code I get<br>
'<br>
Error 3251: The operation requested by the application is not supported by the provide.<br>
'<br>
When AddNew is executed.<br>
<br>
Am i using the correct methods for ADO programming as i'm new to the concept? I get something similar using the Data Environment.<br>
<br>
Thanks Nigel<br>
<br>
Dim adocmdOrderHeader As adodb.Command<br>
Dim WithEvents adorsOrderHeader As adodb.Recordset<br>
Dim db As adodb.Connection, strSQL As String<br>
Set db = New Connection <br>
Set adocmdOrderHeader = New Command<br>
Set adorsOrderHeader = New Recordset<br>
Set adorsOrderDetails = New Recordset <br>
db.CursorLocation = adUseClient<br>
<br>
db.ConnectionString = &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=\\Abc\sys1\USERS\NDR\source\Purchase Order\PurchaseOrder.mdb;&quot;<br>
<br>
db.Open<br>
<br>
strSQL = &quot;SELECT PONumber,OrderDate,Department,SupplierName,SuppliersAddress,DeliveryAddress,DeliveryInstructions,OrderTotal &quot; _<br>
& &quot;FROM tblHeader &quot; _<br>
& &quot;ORDER BY PONumber;&quot; <br>
<br>
adocmdOrderHeader.ActiveConnection = db<br>
adocmdOrderHeader.CommandType = adCmdText<br>
adocmdOrderHeader.CommandText = strSQL <br>
Set adorsOrderHeader = adocmdOrderHeader.Execute<br>
With adorsOrderHeader<br>
.AddNew<br>
!SupplierName = &quot;Nigel Was here&quot;<br>
.UpdateBatch adAffectAll<br>
End With

 

[acruickshank]

You haven't specified a lock type for your record set. It's being opened as read only, and you are trying to add a row.<br>
<br>
Try this code instead of the command object and the record set.<br>
<br>
Set adorsOrderHeader = New ADODB.Recordset<br>
With adorsOrderHeader<br>
.CursorLocation = adUseClient<br>
.CursorType = adOpenKeyset<br>
.LockType = adLockPessimistic<br>
.Open strSql, db, , , adCmdText<br>
End With<br>

 

[NigelRaper]

thanks for that, it does work as you have specified.<br>
However it does not really solve my problem, as I'm triying to use the Data Environment, and the code I used emulated something simular the DE would do (I think). I cannot get my code or the DE to allow any form of updating.<br>
<br>
Nigel

 

[Misled]

It works fine with the DE as well. Commands in the DE automatically have a recordset associated with them preceeded with &quot;rs&quot;. Lets say you have a command called MyCommand. And you have a data environment called DE.<br>
<br>
Dim Rs as Adodb.Recordset<br>
<br>
With DE<br>
.rsMyCommand.CursorLocation = adUseClient<br>
.rsMyCommand.CursorType = adOpenKeyset<br>
.rsMyCommand.LockType = adLockPessimistic<br>
.MyCommand 'Opens the recordset<br>
set Rs = .rsMyCommand<br>
<br>
End With<br>
<br>
<br>
Rs or rsCommand is now updatable. If you had used a lock type of batch optimistic, then you could use disconnected recordsets as well as updatebatch.<br>

 

[NigelRaper]

Thanks I think I now no where I was going wrong.<br>
<br>
Nigel