BobSamonik
asked on
RECORD LOCKING, (VB6 & ACCESS)
I have table T and fields F1,F2,F3,F4
Now, I have to update field F3 and calculate filed F4 based on the values of the fields 1-3
First I select the row from T (SELECT F1,F2 FROM T WHERE ...), then do the caclulation and finaly update (UPDATE T SET F4=... WHERE ...)
But between select and update I have to ensure that nobody changes the fields F1 and F2 before I do the update!
Please, simple example in VB or comment with solution
Now, I have to update field F3 and calculate filed F4 based on the values of the fields 1-3
First I select the row from T (SELECT F1,F2 FROM T WHERE ...), then do the caclulation and finaly update (UPDATE T SET F4=... WHERE ...)
But between select and update I have to ensure that nobody changes the fields F1 and F2 before I do the update!
Please, simple example in VB or comment with solution
You would just say something like:
Update t set F4 = myfunction(f1,f2,f3) where .....
Pete
Update t set F4 = myfunction(f1,f2,f3) where .....
Pete
ASKER
I cant call myfunction through odbc, I use rdo
I was hoping that rdConcurLock in OpenResultset would lock the row but it doesnt
I was hoping that rdConcurLock in OpenResultset would lock the row but it doesnt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is not that simple. F4 is not just function of f1, f2 and f3, it depends od fields in other tables which might to be updated as well. The question was about locking and I've found the answer to this: http://www.developer.com/tech/article.php/10923_721911_1
Even with other tables involved, the solution you chose can only give trouble as you're storing deductable data.
As stated before, when it's complex you can use a function to calculate the value for you based on all fields or you can join tables in your query to get all the needed data.
Pessimistic locking can be done, but can also be rather annoying for your users.
I still advise you to not use field F4. Just imagine what you have to do when the field isn't correct, it will be the basic fields that are wrong, but you can never tell which one, or was the calculation correct and has one of the fields been updated without a recalc....
Nic;o)
As stated before, when it's complex you can use a function to calculate the value for you based on all fields or you can join tables in your query to get all the needed data.
Pessimistic locking can be done, but can also be rather annoying for your users.
I still advise you to not use field F4. Just imagine what you have to do when the field isn't correct, it will be the basic fields that are wrong, but you can never tell which one, or was the calculation correct and has one of the fields been updated without a recalc....
Nic;o)
Why do you have to get the values using a Select query first?
Why can't you just do an update - all the fields are available at that point.
Pete