[RESOLVED] why I can update the recordset?

[lok1234]

Dear all,

Following is my code. I want to know why I can still update the recordset and the result is reflected in the database even I am using adOpenForwardOnly? Thanks

aStrsql = " select ...from ... where ..."

If fn_SqlOpenRS(aRs, aStrsql, adCmdText, gConnect_Main, adUseServer, adOpenForwardOnly) Then
    i = 0
    While Not aRs.EOF
        ' do some calculation..
        aRs!s_totalAmount = aRs!r_totalAmount             
        ..
        aRs!s_last_update_time = Now
        aRs!s_remark = "SW_frmMassUpdate -> f_confirm_Redem -> From Corresponding Redemption"

        aRs.Update
        i = i + 1
        aRs.MoveNext
    Wend
    Call fn_closeADOrs(aRs)
Else
      MsgBox ("Error in opening the query."), vbCritical, gProjectName_SW
End If

[FunkyDexter]

Forward Only has nothing to do with whether or not a recordset is updatable. All it means is that you can only work your way forward through it. So this:-

aRs.MoveLast

whould not work. This performs better than a two way recordset but has the obvious limitations that you can't go backwards.

[Pradeep1210]

Try using adLockReadOnly

[Max Peck]

Try using adLockReadOnly

Why would you do that? He's trying to update the recordset.

-Max

[lok1234]

thanks all.
My question is: Can adOpenForwardOnly perform aRs.update?

[FunkyDexter]

Yes. It can.

[lok1234]

Yes. It can.

Why? Then.. i am confused..
What I think in my mind is that... adOpenForwardOnly only allow "READ" the recordset, but not allow update...

Is this concept totally wrong???????


Then, what is AdOpenKeySet??

Sorry.. I really get confused..

[FunkyDexter]

Is this concept totally wrong

I afraid so.

I think you've completely missunderstood the purspose of adOpenForwardOnly . It's nothing to do with whether or not the recordset is read only. The only affect it has is that it means you can't work backwards through the rescordset. Because it doesn't need to support working backwards it's 'lighter'. By that I mean that it uses less memory and will perform quicker. Most of the time, when you open a recordet, you'll want to start at the beginning and work forward until you reach the end. If that's what you're doing then it makes sense to use adOpenForwardOnly because it'll be a bit quicker and you're not losing anything because you don't want to work backwards anyway.

There are quite a few different qualifiers you can choose between when opening a recordset and they behave in different ways in combination. This site gives quite a nice simplistic view. For a more complete analysis you could check MSDN.

[techgnome]

Think of adOpenForwardOnly as a one-way street.... but I can still go into shops on the street and purchase items....but, I cannot go back to previous shops - it's a one-way street.

adOpenKeyset is a two-way street... allowing me to go forward and backwards up and down the street & visiting any shop along the way.

It's possible to have adOpenForwardOnly & adLockReadOnly .... all the shops on the one-way street are closed ... all I can do is look in through the windows.

It's also possible to adOpenKeyset & adLockReadOnly ... all the shops on the two way street are closed... all I can do is look in through the windows.

Does that help?

=tg

[si_the_geek]

It's probably worth taking a look at the FAQ article What do the parameters of the recordset.Open method mean?, as it gives explanations of adOpenForwardOnly/adLockReadOnly/etc.

[dee-u]

adOpenForwardOnly is a CursorType, not a LockType, so it won't prevent one from an updating a recordset.

[lok1234]

Think of adOpenForwardOnly as a one-way street.... but I can still go into shops on the street and purchase items....but, I cannot go back to previous shops - it's a one-way street.

adOpenKeyset is a two-way street... allowing me to go forward and backwards up and down the street & visiting any shop along the way.

It's possible to have adOpenForwardOnly & adLockReadOnly .... all the shops on the one-way street are closed ... all I can do is look in through the windows.

It's also possible to adOpenKeyset & adLockReadOnly ... all the shops on the two way street are closed... all I can do is look in through the windows.

Does that help?

=tg

a good example. Get it. thanks