UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Split Db Locks Out Other Users, Access 2010    
 
   
dpm1057
post May 8 2019, 11:34 AM
Post#1



Posts: 36
Joined: 29-October 07
From: Boston, MA


Hi, I have been having a problem with Access 2010. I have set up a split database. Backend lives on a Shared Drive, front end is run from the users local hard drive. It was built by first setting up the back end, then starting a new database, linking to the back end tables and building a front end. When any user opens their copy of the front end, all other users are completely locked out. I don't mean seeing the locked file in the same shared drive. Upon opening the database when another user is already in there, we get a message advising that another user has the database open, and everything is disabled. We cannot even view tables. I have gone through multiple variations of the record locking settings, and none of those resolved it.

I have been building split databases for years and this is the first time I have run into this problem. It doesn't matter who opens it first, all other users are locked out.

Anyone else running into this with Access 2010?

Dennis
Go to the top of the page
 
GroverParkGeorge
post May 8 2019, 11:43 AM
Post#2


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


All users who need to use the shared BE must have the proper credentials/permissions for the FOLDER where the BE accdb is stored: Read/Write/Delete.

This is because they must all be able to create, modify and DELETE the locking file Access uses to keep track of which users are connected to it.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dpm1057
post May 9 2019, 07:57 AM
Post#3



Posts: 36
Joined: 29-October 07
From: Boston, MA


Thank you. I have double checked the access rights, made some adjustments and will see what happens.
Go to the top of the page
 
dpm1057
post May 16 2019, 05:00 PM
Post#4



Posts: 36
Joined: 29-October 07
From: Boston, MA


After confirming the users all have the same access levels for the backend on the Shared Drive we are still running into the same issue. Completely at a loss with this one.
Go to the top of the page
 
sumosoftware
post May 16 2019, 05:54 PM
Post#5



Posts: 32
Joined: 3-April 19



I've had a similar issue where the the locking file would remain after all users had exited. I'm not sure what caused it other than having the offenders pc name stored within the locking file, which you can open with notepad.
After having all users exit, I manually deleted the file, double checked folder permissions for shared folder and the issue sorted itself.

Hopefully your solution is just as simple.
Go to the top of the page
 
GroverParkGeorge
post May 16 2019, 07:34 PM
Post#6


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Making sure they have the same rights is half the battle. The other half of the battle is making sure they have read, write and delete rights on the files in that folder. Many network admins don't understand that Access users MUST have delete rights on files in that folder where the accdb resides. The fact that the locking file remains after everyone exits points to that as the problem. Please make sure you confirm that with your network Admin.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post May 17 2019, 01:41 AM
Post#7


UtterAccess VIP
Posts: 1,413
Joined: 4-June 18
From: Somerset, UK


Also check that the database is not being opened exclusively. If that is the case no lock file is created and other users will indeed be locked out.

--------------------
Go to the top of the page
 
ITdarkside
post May 17 2019, 11:11 AM
Post#8



Posts: 69
Joined: 19-May 16



How many users are you having run the front end on their local drive and why are you allowing them to do this?
I have three databases that I admin in this capacity and I always make sure that the front end is in the same folder as the back end just so that when I want to make changes I can be sure that all users are using the latest version.
Go to the top of the page
 
GroverParkGeorge
post May 17 2019, 11:15 AM
Post#9


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Do you mean to say that you let your users SHARE a single FE? Or that this "master" FE is the one they copy to their own desktops for use?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post May 17 2019, 11:27 AM
Post#10


Access Wiki and Forums Moderator
Posts: 75,508
Joined: 19-June 07
From: SunnySandyEggo


Hi Dennis. The default lock settings never gave me any problems before, so I never had a need to mess with them. What would be different in the network environment from those other split databases you created before and this new one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dpm1057
post May 20 2019, 06:19 AM
Post#11



Posts: 36
Joined: 29-October 07
From: Boston, MA


Hi theDBguy,
What you said is exactly why I am so baffled. I didn't make any changes to the defaults. I have been sharing split databases for different solutions for years. It is only recently that I have begun to run into this problem at work. In this case, it is only 3 users, but a fast paced environment. We need concurrent usage.

I think as some other users have suggested it is something to do with the configuration of the shared drive/server. Although the 3 users of this database all have full control over the folder, the backend db file as well as the frontend db file, anyone attempting to open the db gets hit with an error that someone else has it locked and cannot even read from tables, open forms, or run reports. The server is pretty ancient by technology standards (it was in use back in 2007) so I cannot help but wonder if that is a cause of some of these problems.

I am going to look into what other options we have for securely storing the backend.

Dennis
Go to the top of the page
 
gemmathehusky
post May 20 2019, 06:40 AM
Post#12


UtterAccess VIP
Posts: 4,669
Joined: 5-June 07
From: UK


it sounds more likely that the first user is opening the database in exclusive mode.

open access, and then open the database FROM access - and see how the open option is set.

It may be that an access update has changed the default setting. If you only had one user using the database, it wouldn't matter but it seems like you have more than one. (re-reading I see that each user does have a separate copy)

maybe if you open a front end in exclusive mode, then the backend is also linked in exclusive mode?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
gemmathehusky
post May 20 2019, 06:47 AM
Post#13


UtterAccess VIP
Posts: 4,669
Joined: 5-June 07
From: UK


See what happens if you try to open the backend directly, when the database is reporting locked.
Does the ldb/laccdb disappear when the last user closes the database. If not, can you delete it manually?


Also, can you clarify, as I think there is some confusion.

- does each user have a separate copy of the front end, or do users run the same copy on a server? It makes a difference.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
dpm1057
post May 20 2019, 11:40 AM
Post#14



Posts: 36
Joined: 29-October 07
From: Boston, MA


Hi Gemma/Dave,
To clarify, each user has their own copy of the front end that resides on their local hard drive (C:\). This is primarily to ensure no one runs the one on the network drive and corrupts it. If they corrupt their local copy they can always just pull down a fresh copy. If they corrupt the shared drive copy, and I am not in the office, they can't use the database until I return.

I just realized, we have the same problem if someone opens one of the Excel files we use that has data connections to the back end. Even though the connections are not set to automatically refresh and it is a read only connection, simply having the Excel file open will lock other users out.
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 11:44 AM
Post#15


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


All of which point to a permissions problem, still.

As a test, can you put a test copy of the BE in a different folder and try again to have two users connect simultaneously?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 11:45 AM
Post#16


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


Or it may be possible that Excel is the culprit. Does the Access locking problem occur if no one has the linked Excel workbook(s) open?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
gemmathehusky
post May 20 2019, 03:12 PM
Post#17


UtterAccess VIP
Posts: 4,669
Joined: 5-June 07
From: UK


Given that Excel opens read only if it is in use, I wouldn't be surprised if it does the same to external tables. I don't think I have ever opened an access dbs from Excel, so I am not sure.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
tina t
post May 20 2019, 04:03 PM
Post#18



Posts: 5,955
Joined: 11-November 10
From: SoCal, USA


QUOTE
How many users are you having run the front end on their local drive and why are you allowing them to do this?

that's best practice for a multi-user database application in Access: the backend (tables only) sits on the server, and the frontend (all other objects) is copied onto each user's hard drive and used locally.

QUOTE
I have three databases that I admin in this capacity and I always make sure that the front end is in the same folder as the back end just so that when I want to make changes I can be sure that all users are using the latest version.

if you're allowing multiple users to run the same FE database from a server - well, that's strongly advised against, and is a recipe for corruption. best practice is as i said above. next best is for each user to have his/her own copy of the FE db, on the server, with no more than one user per FE copy. this is for situations where - usually an IT mandate - users are not allowed to put any files on their hard drives. it's not desirable because everything has to be pulled from the server, but is still much better than multiple users using the same FE database file at the same time.

there are multiple ways to ensure that all users are using the same/most recent version of a FE database, with both commerical and home-grown solutions available. i think there are a couple right here in UA's archives, and more can be found by googling.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
GroverParkGeorge
post May 20 2019, 07:11 PM
Post#19


UA Admin
Posts: 35,126
Joined: 20-June 02
From: Newcastle, WA


I've done this (use Access data from Excel by connecting to the accdb) several times. It doesn't necessarily lock Access, but it can. The way the connection is written determines that.

Consider these two screen shots.

Attached File  shareaccdblink.jpg ( 414.81K )Number of downloads: 7





--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 08:15 PM