Record Locking (adLockOptimistic) and T-SQL setup

[Geirr]

I need help to evaluate 'best practice' on the following issue:

I'm converting a system from accdb to Sql Server. Sql Tables are Linked to the frontend, using odbc driver. Several users may access the system via separate front-ends for each user.

From time to time, me or the user(s) need to update a big portion of records, and on those routines, I have a habit to do following:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Activeconnection = CurrentProject.Connection

Dim newValue A String

newValue = "some info to be updated"

rs.Open "SELECT dbo_Table.Value, xxxxx FROM dbo_Table", , adOpenKeyset, adLockOptimistic 'xxxxx = other fields to be updated
Do While Not rs.EOF
rs!Value = newValue
'xxxxx other fields to be updated
rs.Update
rs.MoveNext
Loop
rs.Close

The question her (for me at least) is the usage of acLockOptimistic. Because, on several occasion, other user may access one of the record in a form and have it in 'edit mode' when other may run the update mentioned above. So, my question is - how to set the correct rs.open "xxx",, ?, ? values, or - how to avoid 'run-time errors' or something similar?

Secondly - How to convert the above setup - from Dim rs As... to rs.Close to Transaction SQL usage?
I have study the T-Sql theory, and understand much of it, but I'm getting a little lost in the 'With rs.cnn do...' when most of my table updates are made based on similar setup as above. I ask for help on this, because I believe this can also be the solution (at least partly) on the first question.
If someone could 'translate' the above table setup to include T-Sql with error trap on my concern, I will be forever thankful

If the above have been asked and answered so clearly that I want/need, please excuse me for posting this.
I have tried to search, but may have searched the wrong way because I haven't found it...

Thank you for your time!

Best regards,
Geirr E.

 

[Minty]

It would be best to do this in a query as a data set, not by looping through the recordset.
A) It will be significantly faster, with fewer record-locking issues.
B) You could run it as a pass-through query and it will be even faster (assuming a lot of records are involved) and cause even fewer record-locking issues, as it will be handled on the server.

 

[Pat Hartman]

I agree with Minty, an update query will be significantly faster than a VBA loop to update RBAR (row by agonizing row)

When you know you are updating a batch of records where some might be locked by users, DO NOT RUN THE UPDATE. PERIOD!!!!!!

Batch updates should always be run during off hours when there are no active users!!!!

 

[Geirr]

I agree with Minty, an update query will be significantly faster than a VBA loop to update RBAR (row by agonizing row)

When you know you are updating a batch of records where some might be locked by users, DO NOT RUN THE UPDATE. PERIOD!!!!!!

Batch updates should always be run during off hours when there are no active users!!!!

Hi.

Thank you all for replies!
I will use update queries, no probs. But avoiding a 'batch update' as mentioned, will be impossible. I will for sure do what ever I can to reduce the usage.

 

[Pat Hartman]

I didn't say to avoid the batch update. Batch updates are necessary for some purposes. What I said was, run them off hours. This is very easy to do. You don't even need to be there. Your computer just needs to be on. Create a macro that runs the update query. Then using windows scheduler, schedule a task that opens the database and uses the macro argument to run the macro. Make sure that the last step of the macro closes the database. The task can run one time or weekly or whatever you set. You just have to remember to leave the computer on overnight.

If you don't have an easy way to tell if the update was successful, create a query that adds a row to a log table with just the date and time so you use Now() as the value to insert. Run that query after the update query in the new batch macro.

 

[Minty]

If you have full version of SQL Server you can script an Agent job to run the update out of hours automatically on schedule to suit you, assuming it is something that needs doing regularly.

No need for anything clever in the Access front end, and it will always run as your Server should be running 24/7

 

[Geirr]

Hartman and Minty, I did understood you very well. My challenge is that known theory doesn't match reality from time to time.
The case is that my database is a system for cable management, where several status' on the cable are very often updated (as example; registered, measured, routed, cut, fix, connected etc). Some of this changes/updates must be done via a continuous forms, but some of them is more suitable to do on several records at once. But as a very much 'live' system, off-hour updates does not meet the need we have. But again, I agree with your saying, but as I write her - it's doesn't match our need in the sense of practical usage.
So my goal was to try to find 'middle-way' out of this - so to speak. That's way I put in T-Sql in my initial message because of the rollback functionality (if needed). - and mentioned because I haven't got the fully understanding of using T-Sql....

 

[Minty]

If it's live updates then use a pass-through query to the server to do as an update query.
It will handle the record locking much more elegantly, and unless someone is actively writing to a record you are updating you shouldn't get any conflicts.

Another hint - unless someone needs to edit a record e.g. a viewing list, open continuous forms as read-only Snapshots, they will open quicker, and you won't get any record-locking issues.

 

[gemma-the-husky]

I have some very complex databases, and I have never had to use any record locking.
Only very occasionally have I seen genuine "another user changed the record" type things.
Most of the time it's a programming error.

I would be surprised if an update query every gave you a record locking error. I suspect the active user would get the error when the update query caused the active user's record to report that it had changed.

If you use active record locking then you also need sophisticated recovery processes to clear the resolve the impasse it causes.

 

[Pat Hartman]

If you use the default locking for forms which is "optimistic". That means that the displayed record isn't locked until Access actually tries to write the changed data back to the table. So, if you are also running batch updates, Dave is correct, it is the on-line user that will get the conflict message. I think there might even be retry logic in the database engine for action queries if it comes across a record that is locked at the same split second that the action query is trying to process it.

Usually the conflict error occurs because the programmer does not include code to force the the current record to be saved before running the update query which affects the current record.

The reason we don't see a lot of conflict messages in other situations (non programmer error) is because in most applications, the users aren't just randomly updating records, they are working on a task. The customer called and needs to change his address, add an order, etc. so, it would be really unusual for two users to need to simultaneously update the same record at the exact same moment in time.

 

[Geirr]

Hi all.

Thank you all for replies and suggestions. I've started over for the whole project to change all instances using the code I mentioned in my first message to use action queries for add, update and delete records. Then we will se an improvement - I hope...

Brg. Geirr.