Current Recrdset does not support updating

[jamesnkk]

I got this Run-time error 3251

Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.


Dim red1 As New ADODB.Recordset
Dim strmess As String

Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)

If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update

End If
red1.MoveNext
Loop

End If

How can I resolve this, why MS Access does allow this simple updating ?

[sashi]

I got this Run-time error 3251

Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.


Dim red1 As New ADODB.Recordset
Dim strmess As String

Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)

If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update

End If
red1.MoveNext
Loop

End If

How can I resolve this, why MS Access does allow this simple updating ?

Hi there,

Kindly refer to below modified code segment, hope it helps. Good luck & Take care.

  strmess = ""
  strmess = "SELECT * from tblinvoicelines  WHERE invoice=" & newInvo
  red1.Open strmess, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
 

[ADezii]

I got this Run-time error 3251

Current Recrdset does not support updating. This may be limation of the provider, or of the selected locktype.


Dim red1 As New ADODB.Recordset
Dim strmess As String

Set cnp = CurrentProject.Connection
strmess = "SELECT * from tblinvoicelines WHERE invoice=" & newInvo
Set red1 = cnp.Execute(strmess)

If Not red1.EOF Then
red1.MoveFirst
Do While Not red1.EOF
If red1!Invoice = newInvo Then
red1.Fields("amount") = Round(red1!Qty_Order * red1!Sell_price, 2)
red1.Update

End If
red1.MoveNext
Loop

End If

How can I resolve this, why MS Access does allow this simple updating ?

If I am not mistaken, and unless otherwise specified, isn't the Default ADO
Recordset Read Only..

[sashi]

If I am not mistaken, and unless otherwise specified, isn't the Default ADO
Recordset Read Only..

Hi there,

Yes, the default locktype is set to adLockReadOnly. This property is set to read/write on a closed recordset and read-only on an open recordset. Take care.

[jamesnkk]

Hi there,

Kindly refer to below modified code segment, hope it helps. Good luck & Take care.

  strmess = ""
  strmess = "SELECT * from tblinvoicelines  WHERE invoice=" & newInvo
  red1.Open strmess, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
 

I copy your statement, I got an error message - Run time error - 3705
Operation is not allowed when the object is open.

[willakawill]

I copy your statement, I got an error message - Run time error - 3705
Operation is not allowed when the object is open.

that is because you have already opened the recordset earlier in your code.
insert this just before the code that you posted above:
red1.Close

[willakawill]

BTW it is much better to explicitly initialize your recordset like this:

Dim red1 As ADODB.Recordset
Set red1 = New ADODB.Recordset

If you do it this way:

Dim red1 As New ADODB.Recordset

each time you use red1 it will be checked to see if has been initialized which is inefficient

[jamesnkk]

BTW it is much better to explicitly initialize your recordset like this:

Dim red1 As ADODB.Recordset
Set red1 = New ADODB.Recordset

If you do it this way:

Dim red1 As New ADODB.Recordset

each time you use red1 it will be checked to see if has been initialized which is inefficient

Wow !, Thank so much for your coding, I got it work now !, and Thank so Much for the explanation. Also Thank Sashi, Thank God, I came to the right forum.

[sashi]

Wow !, Thank so much for your coding, I got it work now !, and Thank so Much for the explanation. Also Thank Sashi, Thank God, I came to the right forum.

Hi there,

Good luck & Take care.