By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Current Recrdset does not support updating

100+
P: 134
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 ?
Dec 23 '06 #1
Share this Question
Share on Google+
8 Replies


sashi
Expert 100+
P: 1,754
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.

Expand|Select|Wrap|Line Numbers
  1.   strmess = ""
  2.   strmess = "SELECT * from tblinvoicelines  WHERE invoice=" & newInvo
  3.   red1.Open strmess, CurrentProject.Connection, _
  4.             adOpenKeyset, adLockOptimistic
  5.  
Dec 23 '06 #2

ADezii
Expert 5K+
P: 8,597
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..
Dec 23 '06 #3

sashi
Expert 100+
P: 1,754
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.
Dec 24 '06 #4

100+
P: 134
Hi there,

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

Expand|Select|Wrap|Line Numbers
  1.   strmess = ""
  2.   strmess = "SELECT * from tblinvoicelines  WHERE invoice=" & newInvo
  3.   red1.Open strmess, CurrentProject.Connection, _
  4.             adOpenKeyset, adLockOptimistic
  5.  
I copy your statement, I got an error message - Run time error - 3705
Operation is not allowed when the object is open.
Dec 24 '06 #5

100+
P: 1,646
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
Dec 24 '06 #6

100+
P: 1,646
BTW it is much better to explicitly initialize your recordset like this:
Expand|Select|Wrap|Line Numbers
  1. Dim red1 As ADODB.Recordset
  2. Set red1 = New ADODB.Recordset
If you do it this way:
Expand|Select|Wrap|Line Numbers
  1. Dim red1 As New ADODB.Recordset
each time you use red1 it will be checked to see if has been initialized which is inefficient
Dec 24 '06 #7

100+
P: 134
BTW it is much better to explicitly initialize your recordset like this:
Expand|Select|Wrap|Line Numbers
  1. Dim red1 As ADODB.Recordset
  2. Set red1 = New ADODB.Recordset
If you do it this way:
Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 24 '06 #8

sashi
Expert 100+
P: 1,754
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.
Dec 26 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.