How do I keep this database from locking?

[Jesse Jones]

I currently have an access database that is a 2007 file that is operated through Access 2010. It has as many as fifty users on it at any given time. All of the user's workstations are set to lock edited records in their Access client settings. The problem is that it is routinely locking up still.

Some background: it is not a problem with more than one user accessing the same record. They are all divided to not allow this and when we check when it locks, no one has ever been on top of another user.

The database is split.

The front end database only allows access to one form. In this form is a subform.

The main form shows client information. The subform contains call records to that client. They are linked by the unique client ID.

What I cannot explain: Two of the fields on the subform are combo boxes that record codes for the call. The first stores a simple yes/no to whether or not we make contact. The second stores the code of the client's response. By default, the contact yes/no shows no and the code shows "NOM" (no offer made). If either of these are changed, the database will never lock. If they are left (which they usually are as we only make about 23% contact with clients), the database will say "could not update; currently locked."

This is a ridiculous problem that I cannot find a solution for. Please, anyone, you're help would be greatly appreciated! Thank you.

[beacon]

Hi Jesse,

What happens if you remove the default for the combo boxes?

[Jesse Jones]

Not sure. I'll do that tonight when I can get exclusive access.

I just found out that it is locking even those fields are changed. I was misinformed on that one.

[Jesse Jones]

No difference at all. It happens on the record exit.

[beacon]

In the 3rd post in this thread you said:

I just found out that it is locking even those fields are changed. I was misinformed on that one.

So the problem occurs whether you change the value or not?

What is the "Default open mode" and the "Default record locking" settings (Tools->Options->Advanced) for the back-end of the database? I know you said that the front-end is set to allow edited records, but it's worth checking to make sure the back-end settings coincide. I've never seen a front-end and back-end of a split database differ, but it's worth a shot.

[Lysander]

Does it lock with only one user on the system? If so, then it would be a problem with your form and subform. I have had situations developing code where my form would not save (record locked) due to a sub-form or query trying to change the same record at the same time.

[Jesse Jones]

Nope. It only locks when there are multiple users. Both the form and the subform run off of queries, not directly from the table.

The query the form builds from requires parameters as soon as it is opened, so it is impossible for two users to be on the same record (unless they enter the wrong parameters which no one has done).

I'm completely stumped. Would page-level locking help?

[Lysander]

Oh, I was hoping for a simple form error. I use a multiple-user database in Access 2003 and have never had locking problems, but then I only have 6 users. Years ago I did a system for NatWest in Access 2 that had over 400 users and we had massive locking problems. We had to go for unbound forms in the end and handle the locking ourselves, which is a very complex route to go down.

Been ages since I dealt with locking issues but I seem to remember there was a way to tell if a record was locked before starting to edit it.

I'm afraid I am going to have to leave this one to someone more upto date on locking issues than me.

[Jesse Jones]

Okay. A few updates: both the form and the subform were working directly off of the tables. I have since put queries underneath them. I was hoping this would help, but it hasn't.

Both the front end and the back end are default opened as "shared" and "edited records" is the default lock settings. I tried yesterday to change to page-level locking instead of row-level locking hoping that this might bring a solution. It didn't.

What I would like to do is set the form to pull one record out of the tables, edit it and then snap it back in. I don't think it is doing this now for this reason: I designed a pop-up form that is an exact replica of the main form. The difference is that this is based off of a new query that asks for a Client ID parameter when it opens. Pretty simple stuff. If I have the main form open to say client 35798, and I open the other form and enter client ID 35789 make a change and then save that change, it is instantly updated on both forms. This tells me that the main form is always pulling information from the queries underneath it. I'm thinking that if I can get it to only pull information for one record when the form is opened, and then put the new information into the table when the record is changed, that would help. Essentially, I don't want the information that shows on the form to be pulling in real-time from it's query. I want it to extract one record and close the connection to the query behind it. Then when we go to the next record, put the changes back into the query underneath it and pull the next record. Does that make sense? Could anyone help me with this? I think that this may help.

[Lysander]

Ok, that makes sense and if I understand you correctly, it is what I did in my NatWest project. i.e. Use unbound forms. Unfortuantly that was over 15 years ago and I no longer have the code.

Basicaly, the form is not bound to any record set, nor are any on the controls.

In the on-open event of the form you open a recordset containing the one record you want to look at, loop through the fields in the record set and populate your controls, then close the record set.

You can then edit away to your hearts content. When ready to save, you open the recordset again and update the changed values.

When you try to update the record, you need error handling to test if the record is locked, if so, wait a few mili-seconds and try again.

This way, the underlying data is never locked except for the few milliseconds you are saving the data.

Sorry I can't give you the code I used, as I say it was years ago, and was in Access 2.0 anyways.

[Jesse Jones]

Yup, that sounds complex, but if it works, I'm certainly willing to figure it out. Thanks for the help!