Access 2010 Locking errors on a split database

Question

I created an mdb database with up to 10 users.  It was originally NOT split and they all ran it using shortcuts to the database.  It ran great for 2 years until it started slowing dramatically in the last year.  I then split the database AND converted it to an .accdb.  Backend is located on server on Y: that they all have access to.  Front end is also located on Y: but each user has a copy of front end on their desktop.  They now get locking issues throughout the day.  I can't seem to pinpoint what is causing these issues. Users are adding and editing records all day but never edit the same record.  What happens is they are in the middle of editing a record and they freeze and get the record locked symbol.  They have to close and reopen the form to clear.  

All users have: 1) read/write/delete rights to drive where copy of fe and be are located 2) Autocorrect options are turned off 3) Locking is set to No Locks in options and in the form properties 4) All forms are based on queries (not tables - changed this because thought could be the problem) 5) I ran some tests and some users have same user name in windows and not sure if this is a problem.

Additional Info: The main form has control buttons that open subforms and filter settings are in properties of sub-form to filter for the related key from main form 

Both the client and myself and now getting frustrated.  Has been going on for 2 weeks.  Can anyone give any additional ideas that I can try to get past these locking issues?? HELP!

Answer 1

Users with same name will not matter. As for relationships, they are always set in the back end database.

You can certainly “view” the relationships in the front end, but they don’t matter and again this would have nothing to do with your existing issues.  (so if you open the relationship windows you can choose show tables – but as noted, all relationships are set and managed in the back end database – the front ends cannot change those settings anymore and thus what you see in the front end relationship window does not matter anymore).

It not clear why with locking turned off that some users cannot edit data. They should be able, but if they are editing the SAME data then could (and should) receive the dreaded this data been changed by someone else message if other users are editing + changing the same data.

I would again double check that some forms (maybe the sub forms) are not set to lock the whole table, or lock a record.

Keep in mind that all users need full rights to the back end folder. That means file create rights, file delete rights. If “some” users don’t have these rights, then Access cannot create the locking file in the back end folder and Access WILL THEN open the database in READ only mode (so users will not be able to edit data). So I would check + ensure that everyone has full rights to the folder where the back end accDB file resides - users need file CREATE rights and delete rights, else Access cannot create the back end locking file and thus defaults to "read only" operation.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

In general, since users are on “different” records, then you will rare see the “locking” symbol.

However in a sub form with “small” records, then two users working on a different “main” record and editing (or adding) the sub form record “can” in some cases cause what we call a “page lock”.

The above is a fancy way of saying that Access will “lock” that page in question to update data, and records from “different” users can be in that same page.

A possible solution is to turn on what is called “row” locking. I shall warn that this feature will cause signification BLOATING in the back end database, but it certainly would be worth a try in your case.

The option is found via file->Options->Client Settings

The option you want to try is

“Open database by using record-level locking).

Note that you can leave settings above "default" as to NO locks for default record locking. So EVEN WITHOUT ANY locking set, the record-level setting WILL force records to be placed in their OWN page, and thus no locking conflicts will occur.

ALSO NOTE that the above settings are a default. You will NOT see a lock symbol in a form UNLESS you set the form to lock edited record (or perhaps you changed a form to table locking). So it somewhat confusing, since setting the above options will NOT change how a form works unless you open up the form in design mode, and in the data properties tab check (or set) the locking choice.

In effect the above 3 settings are ONLY for new forms default, the existing forms are not change. However the above last option of Open database by using record-level locking IS GLOBAL.

I suspect this is only occurring when editing very small tables, since then a “lot” of records can be packed into one database page. The above option forces records to be placed in their own page. So this solves the “conflict” issue, but as noted will cause a substantial increase is back end file growth (bloat). As a result you have to adopt an “increased” frequency of compacting the back end database.

Also, the fact that users received a “lock” symbol in the record selector should NOT cause a freeze up, so there certainly something else here going on that does not seem quite right???. And users should NOT see the lock symbol UNLESS the form is set to lock edited records. If the form is set = no locks, then they will see the “edit pencil” and receive an error that the records been changed by someone else AFTER they edited the record. If you have locking = on, then they see a lock symbol RIGHT away and cannot edit the form. And if the form is record locked, then even VBA code that attempts to set or edit data likely can and will freeze up. So I would for first test REMOVE any locking settings you have on a form - you should NOT be seeing that lock symbol unless the form in question has locking = on.

So from your current description, you have that form (the sub form) set as row locking = table or edited record. Perhaps change that setting to none.

So I would check the form(s) in question, and see what the locking settings are. It not possible to see locking symbols unless the form has locking set.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 3

Also just noticed I lost all relationships when I converted.  This and the users having same user name in windows are my only 2 theories right now.  Any thoughts on these 2 problems or any more suggestions?  I'm willing to try anything even changing the code if need be.

Answer 4

Just checked the global setting and it is already turned on.

Answer 5

Thank you so much for replying.  And you are right...users were seeing the lock symbol until I recently set all form properties to no locking.  No they don't see the symbol but they can't edit the record.  I have made so many changes every night to see which one fixes the locking problem!  I will now try setting the global option you talked about above and see what happens.  Thanks again for your help!