How to Update data in resultset?

[briansmithdanisco]

I have a module that does a select and returns a resultset with 1 to many records. I loop through the records and do some time calculations. I need to update each of the recrods in the resultset with the correct calculated time. I think I need to set the LockType to adLockOptimistic, but when I do, I still get the error that my resultset is read only.

Can anyone suggest where to put the LockType line so I am able to update the resultset data?

Here is an example of my SELECT. I have tried several combinations of SET commands, but nothing seems to be working.
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command

rst.LockType = adLockOptimistic

' Get all the on-stream records for this batch number.
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "Select * from tblOnStreamTime " & _
"Where '" & Me.MainBatchNumber.Value & "' = tblOnStreamTime.MainBatchNumber;"
.CommandType = adCmdText
.Prepared = True
Set rst = .Execute
End With

After I do some calculations I try to update the time value in the resultset with the calculated time:
rst!TotalTimeDetail = dteTimeCalc
rst.Update

Any suggestions would be appreciated.
Thanks.

 

[zevw]

Look at the following thread

thread705-1357831

I think it might help you

 

[JerryKlmns]

No need for the command object.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
   .ActiveConnection = CurrentProject.Connection
   .CursrorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Source = "SELECT * " & _
             "FROM tblOnStreamTime " & _
             "WHERE MainBatchNumber='" & Me.MainBatchNumber.Value & "'"
   .Open
End With

'do some calculations 

With rst
   .Fields("TotalTimeDetail")= dteTimeCalc
   .Update
   .Close 
End With
Set rst = Nothing

If MainBatchNumber is NOT of text data type field then your WHERE clause should be
"WHERE MainBatchNumber=" & Me.MainBatchNumber.Value

 

[briansmithdanisco]

Thanks JerryKlmns!! It worked perfectly! I am self tought with VBA so sometimes even though something works, I am not 100% sure why.

If I am working in the same database with selects on tables, I don't need to create a new command object each time? I have always used command and result set objecst in my selects, updates, deletes...

Thanks again for your help.

 

[JerryKlmns]

< I am self tought with VBA
That makes us two [cheer]

<If I am working in the same database with selects on tables, I don't need to create a new command object each time?

Command object is used with parameter saved queries, to pass the parameter(s) value(s)

For action queries use the Execute method of a connection object (CurrentProject.Connection is one), along with the option adExecuteNoRecords=128. If you execute an sql statement created on the fly use adCmdText=1. Those two dogether sum up to the value 129

CurrentProject.Connection.Execute strSQL, , 129

 

[JerryKlmns]

< I am self tought with VBA
That makes us two [cheerS]

<If I am working in the same database with selects on tables, I don't need to create a new command object each time?

Command object is used with parameter saved queries, to pass the parameter(s) value(s)

For action queries use the Execute method of a connection object (CurrentProject.Connection is one), along with the option adExecuteNoRecords=128. If you execute an sql statement created on the fly use adCmdText=1. Those two dogether sum up to the value 129

CurrentProject.Connection.Execute strSQL, , 129