Unbound Multi User DAO Recordset locking

[ironfelix717]

Hi all,

I'm trying to ensure I fully understand how locking will work in a multi-user access environment (5-10 users) and to plan my code around it before I continue development.
This question is in reference to unbound forms. How do you pros write your code to handle conflicting updates, etc?

I came across a very interesting article that discusses how to handle concurrent update conflicts. He uses 2 routines that loop and try to edit/update the record until success is reached. here So that is an interesting approach. It appears the consensus among experienced devs is that conflicts rarely occur in a 5-10 user environment, so using optimistic locking is ideal.

I thought I would simulate one of these multi-user concurrent update/edits, but I wasn't able to produce any errors in quick testing. The attached db edits/updates a record in a table. Making 2 copies and opening them - I was not able to force a conflict and even played with the LockType on the RS object (which i know has an effect on this issue).

----------

To be clear: I am just trying to understand how you would professionally handle code that is meant to use recordsets and ensure there are no conflicts with other users, if there is, handle the error elegantly. I would rather develop this into the project early, than deal with it at a later date.

Thanks

 

[theDBguy]

Hi. I was just curious why you want to use unbound forms. What is wrong with using bound forms?

 

[ironfelix717]

Hi. I was just curious why you want to use unbound forms. What is wrong with using bound forms?

Hi, there are several reasons to use bound several reasons to use unbound. This topic is about unbound.

 

[theDBguy]

Hi, there are several reasons to use bound several reasons to use unbound. This topic is about unbound.

Like I said, I was just curious...

 

[ironfelix717]

**UPDATE

Greetings,
So, I had a dumb moment (as I often do). The example above, when cloned, just uses the local table, not a common backend, so obviously there wasn't a conflict (its not modifying the same table). That was dumb. Apologies if that confused anyone.

I have since cobbled together a front end + back end. The front end has the code that attempts to edit the recordset and then update it.
Cloning the front end and trying to create a concurrency issue can easily be done by trying to edit the record simultaneously on both front-ends.

I slammed together a sloppy routine that checks to see if a recordset is in .EditMode and if so, just loops until edit mode is 0. This doesn't work though. There is still a lock on the table and checking for LockEdits didn't help me. I suspect the lockedits / locktype properties (confusing enough as they are) have an effect.

Routine:

Private Function TryEdit(rec As Recordset) As Boolean
'TRUE = successfully invoked edit mode
'FALSE = failed
Dim i       As Long

Debug.Print "Trying to edit...."
'WAIT UNTIL NOT IN EDIT MODE
Do Until rec.EditMode = 0
    i = i + 1
    Debug.Print i
Loop

'EDIT
rec.Edit
TryEdit = True
Debug.Print "Success"

End Function

--------

As much as I read on the locktypes, lock edits, all I get is more confused when dealing with DAO recordsets, so any insight there on how THAT effects this handling would be insightful.

Again, my CORE question: i'm asking the professionals out there who having managed recordset based unbound forms how they are writing their code to successfully deal with concurrency issues that may arise. I want to get this correct from the start.

@The_Doc_Man - Your expertise appreciated here.


-Thanks!!!!

 

[theDBguy]

Hi. I am very sorry if I seem to have hijacked your thread by continuing to post unhelpful replies.

Whether I use a bound or unbound form, if I wanted to create something like what you're saying to avoid write conflicts on multi-user environment, I would probably use a flag to indicate a record is in edit mode.

If we could step away from VBA for a moment, you know how when you open an Excel spreadsheet or a Word doc that somebody is already editing and you get a warning (or at least you used to)? That's sort of what I am thinking to do.

Hope that makes sense... (I didn't want to say too much, in case it's not helpful)

 

[GK in the UK]

OK, caveat, I'm not a software or Access professional, in fact I'm not much beyond beginner.

I don't think it makes any difference whether my recordset is bound or unbound. I handle the locks and the ability to edit it in code.

Here's how I handle record locking. I've tested it by, as you say, cloning a copy of the FE.

I have a table tblLocks with:
lockID, long but not AutoNumber.
lockTime
lockByUser
lockComputerName

When I open a record it opens in 'view' mode. There are no limitations on any user opening records in 'view' mode. So all users could be viewing the same record.

One user clicks the button labeled 'Edit'.
I attempt to place a record in tblLocks with the ID of the record that the user is trying to lock. tblLocks is in the BE.
Since lockID is the primary key of tblLocks only one user can lock the record. If two users attempt to lock it at near-instantaneous moments, I presume that Access will allow only one ID to be placed in tblLocks.

If no error is raised, the record that was being 'Viewed' is now placed in 'Edit' mode. The 'Edit' button now reads 'Save' and I set the 'Enabled' property of the various editable controls to Enabled = true. This changes them from greyed out to white so it's a good indication that the fields are now editable.

If Access raises an error, which it will if I attempt to place a duplicate ID into tblLocks, a message pops up which says:

"Record in use by user 'GK' since 18:30 13/08/2020 " and the form remains in 'View' mode.

The user with the successful lock can edit and update the record and no other user can do the same.

Upon pressing the Save button, the edited record is saved (bound or unbound, don't see how it can matter) and I delete the record with that ID from tblLocks. So it is now editable by any other user, if they press 'Edit'. The form sets the command caption back to 'Edit' and sets the editable controls to 'Enabled = false' which greys them out.

I make no attempt to use the built in Access locking ability. When the user attempt to Edit a record, my code tries once only to lock the record by placing the ID into tblLocks. It's up to the user to keep trying. In my application, it wouldn't make a lot of sense to keep trying; typically, a user will open a record (an invoice, say), place it into 'Edit' mode, then spend more than a few seconds editing line details. So the repeated attempts to lock would halt progress until the other user has finished.

I'm toying with counting the time elapsed when a user places a record into 'Edit' mode and popping up a message if it looks like someone has 'Locked' a record and forgotten about it.

Is this the 'Correct way to do it' ? I don't really know.

 

[Cronk]

I've never had to worry about locking with unbound forms because I deliberately don't set up unbound forms for users to edit data. I use bound forms and let Access handle locking. Your method is slower and will bloat faster with the adding/deleting of records.

Being an unbound form, you can't just "save the record", you need to save the value of each individual text box, combo ...

 

[ironfelix717]

@theDBguy @GK in the UK
Thanks for your input. Flags is something i've already considered. I would highly recommend avoiding that method. Its not an approach that is ideal. What if the system crashes, power goes out, and a flag is left ON ? I can already hear the users crying "Someone call the Admin to free the lock. its stuck again." As the Experts-Exchange author laid out, the most elegant way to to handle this is via waiting/checking the edit. (at least that i have found)

@Cronk

I've never had to worry about locking with unbound forms because I deliberately don't set up unbound forms for users to edit data. I use bound forms and let Access handle locking. Your method is slower and will bloat faster with the adding/deleting of records.

Bloating? Unbound performs faster in almost every single way (aside from development). This topic isn't about unbound versus bound. If you want to make that argument, open a thread. This isn't the topic.

 

[Pat Hartman]

Access is a Rapid Application Development (RAD) tool. If you are not going to use the RAD features such as bound forms, you shouldn't be using Access as your FE at all. You are saddling yourself with all the baggage of using a "heavy" tool and taking none of the benefits (less development work)

Again, my CORE question: i'm asking the professionals out there who having managed recordset based unbound forms how they are writing their code to successfully deal with concurrency issues that may arise. I want to get this correct from the start.

I don't think you'll find many "professionals" who are using unbound forms for a Jet/ACE BE.

 

[ahmed_optom]

If your using an unbound form, you dont have to worry about multi user access issues.

I take it this is for data entry?

For example, when the user clicks your save/add button, then you will need to vba a new record and all data will be stored on the table at that instant.

I used to have a lot of issues with recordlocking etc, I minimised these by using unbound forms and where that is not an option, I have a simple column called recordlocked on the table and if true, then other users gets a prompt saying "user xxx is on this record" etc.

If you dont design the database around the issues, you will become very familiar with this annoying prompt.

 

[CJ_London]

Unbound performs faster in almost every single way (aside from development)

agree it takes longer to develop, but with regards 'every single way' providing forms are designed the right way I don't agree. with an unbound form you need to communicate with the BE to fetch the data, again to lock and again to update and then unlock. Can you provide some examples which demonstrate they are faster

 

[The_Doc_Man]

This is a case where Murphy's Law applies: "If it can go wrong, it WILL go wrong." However, most people forget the SECOND sentence of Murphy's Law. "So engineer it in a way that it cannot go wrong."

Using bound forms and carefully NOT specifying things until the last possible moment, and also by carefully NOT specifying restrictive locking schemes, you can "play the odds" to avoid data collisions. But you have to think about this in a highly analytical way. You have to ask "Where can this go wrong?" Think about using AUTONUMBER cases where possible. Allow Access to help you. Then identify the hot spots and see if you can come up with a SIMPLE way to prevent damage. The more complex you make it, the worse off you will feel, and you will have that nagging feeling that your database won't respect you in the morning. (Sorry, I get colorful sometimes...)

I had a system with not 5-10 but more like 30-40 users in a situation that had to be precise because it dealt with security issues. I.e. we were literally tracking how secure our server farm was on a given day. No room for error, but I used bound forms for everything in sight because Access locking was able to do what was needed to keep it all separate for me.

This is a divide-and-conquer situation where the division will be along the lines of "where can interference occur and how do I prevent THAT case?"

 

[isladogs]

I've stayed out of this thread precisely for the reasons mentioned by @Pat Hartman
I am a professional developer and almost never use unbound forms.
They have almost no advantages and many disadvantages

Like @CJ_London, I'm also struggling to think of any occasions where an unbound form would be faster