Users Locked out of Shared Databases

[jerryb123]

We have a database that has been up and running for some time. We are not
running any security measures or permissions on it, but for some reason when
one user is in it, other people will sometimes get messages saying the
database has been put in a locked state. This wasn't happening previously,
and we had set everything up so multiple users could be in it at the same
time, and we don't have a lot of users in it at any one time. (4-5, usually.)

Do problems like this happen when some people are running Access 2003 and
some are running 2007? What should I do to troubleshoot?

 

[Joan Wild]

The most common reason this happens is because of Windows permissions.
Perhaps a change was made on your server. All users need read, write,
create, and delete permission on the folder where the mdb is located (delete
isn't absolutely necessary, but will cause less grief).

Also is the database split - it should be (even if everyone were on the same
version).

 

[IndyRSX]

I am having the exact same problem. I have a shared access database that has
2-3 users in it. I never had a problem until recently, but now, only one
person can be in it at a time. Nothing has changed with user permissions on
the server that the database is shared from. Folder permissions are the
exact same they have been for years. I have other databases that are shared
that are not having problems, just this one. Any suggestions?

 

[Joan Wild]

I am having the exact same problem. I have a shared access database that
has
2-3 users in it. I never had a problem until recently, but now, only one
person can be in it at a time. Nothing has changed with user permissions
on
the server that the database is shared from. Folder permissions are the
exact same they have been for years. I have other databases that are
shared
that are not having problems, just this one. Any suggestions?

Are you absolutely certain that nothing has changed on the server (perhaps
without your knowledge). If you are, then with everyone out of the mdb,
check the folder and delete the ldb file that may be hanging around; then
backup the mdb, and compact it.

 

[jerryb123]

I think someone had changed the settings. It seems to be working now. Thanks
for all the responses!

 

[IndyRSX]

Are you absolutely certain that nothing has changed on the server (perhaps
without your knowledge). If you are, then with everyone out of the mdb,
check the folder and delete the ldb file that may be hanging around; then
backup the mdb, and compact it.


--
Joan Wild
Microsoft Access MVP

Thanks for replying Joan. I checked to make sure all the permissions were still the same. These users have full control over this network share as well as the database. I also ran a compact and repair, but they are still having the problem. Basically, if one user opens the db, the other user cannot. It says it is locked by the first user. If the first user closes out and the second user opens the db, the first user cannot get back in. This problem just started happening a couple of weeks ago, but I cannot for the life of me figure out why. We have multiple dbs on the same share that are allowing multiple users, but this one is not. Any other suggestions would be greatly appreciated. Thanks.

 

[IndyRSX]

 

[Joan Wild]

Thanks for replying Joan. I checked to make sure all the permissions were
still the same. These users have full control over this network share as
well as the database. I also ran a compact and repair, but they are still
having the problem. Basically, if one user opens the db, the other user
cannot. It says it is locked by the first user. If the first user closes
out and the second user opens the db, the first user cannot get back in.
This problem just started happening a couple of weeks ago, but I cannot
for the life of me figure out why. We have multiple dbs on the same share
that are allowing multiple users, but this one is not. The exact error is
"The database has been placed in a state by user "Admin' on machine
<computer name> that prevents it from being opened or locked. No one is
opening the db with exclusive access. Any other suggestions would be
greatly appreciated. Thanks.

Get everyone out of the database and go to the folder where it's housed. If
you see a file there with the same name as your mdb but has a ldb, delete
it. Then backup the mdb. Now open the mdb and Compact and Repair it.

You should split any databases that you have. Keep the backends (mdb with
tables/relationship only on the server) and give each user a copy of the
frontend (all other objects with links to the backend mdb tables) on their
PC.
More on splitting:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
http://granite.ab.ca/access/splitapp/index.htm

 

[IndyRSX]

Get everyone out of the database and go to the folder where it's housed. If
you see a file there with the same name as your mdb but has a ldb, delete
it. Then backup the mdb. Now open the mdb and Compact and Repair it.

You should split any databases that you have. Keep the backends (mdb with
tables/relationship only on the server) and give each user a copy of the
frontend (all other objects with links to the backend mdb tables) on their
PC.
More on splitting:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
http://granite.ab.ca/access/splitapp/index.htm

The ldb file was deleted already when the user closed out of the db. I then
did the compact and repair which didn't work. Just now, I made a copy of the
db and split it. I then had user1 log into it, which worked fine. Then I
had user2 log in. The same error came up. I also tried to create and mde
file, but access returned the error: Access was unable to create an MDE
database. This error is usually associated with compiling a large database
into an MDE file. This particular db only has 5 forms, 2 of which are
subforms so I can't imagine it's that big. Any other suggestions? Thanks
again.

 

[Joan Wild]

The ldb file was deleted already when the user closed out of the db. I
then
did the compact and repair which didn't work.

Didn't work? Did you get an error message? What version of Access is the
file. Are users using mixed versions? Is everyone up-to-date with service
packs?

Just now, I made a copy of the
db and split it. I then had user1 log into it, which worked fine. Then I
had user2 log in. The same error came up. I also tried to create and mde
file, but access returned the error: Access was unable to create an MDE
database. This error is usually associated with compiling a large
database
into an MDE file. This particular db only has 5 forms, 2 of which are
subforms so I can't imagine it's that big. Any other suggestions? Thanks
again.

Open the frontend, hit Ctrl-G and then Debug, Compile. Fix any errors it
finds. Continue to Debug, Compile until you get no errors. Then Compact
it - this should not fail.

You mention 'log into it' - is this database secured? If so, is the mdw
file named the same as your mdb? That's not a good idea, as the mdw needs
to create a ldb, as well as the mdb - if they are both named the same, then
it can't.

 

[IndyRSX]

Didn't work? Did you get an error message? What version of Access is the
file. Are users using mixed versions? Is everyone up-to-date with service
packs?


Open the frontend, hit Ctrl-G and then Debug, Compile. Fix any errors it
finds. Continue to Debug, Compile until you get no errors. Then Compact
it - this should not fail.

You mention 'log into it' - is this database secured? If so, is the mdw
file named the same as your mdb? That's not a good idea, as the mdw needs
to create a ldb, as well as the mdb - if they are both named the same, then
it can't.

I'm sorry, the compact and repair DID work. Not sure why I wrote didn't.
The access db is in 2000 format. Everyone is using Access 2003 with the
latest service pack. When I tried doing the split and mde, I had to upgrade
the copy I made to test to 2003 format. That's when I got the same error
after splitting. So I don't think it is a format issue. I'll try doing the
debog and compile instructions to make the mde. As for the logging in part,
it is just password protected; there is no mdw or user permissions. Thanks

 

[IndyRSX]

I'm sorry, the compact and repair DID work. Not sure why I wrote didn't.
The access db is in 2000 format. Everyone is using Access 2003 with the
latest service pack. When I tried doing the split and mde, I had to upgrade
the copy I made to test to 2003 format. That's when I got the same error
after splitting. So I don't think it is a format issue. I'll try doing the
debog and compile instructions to make the mde. As for the logging in part,
it is just password protected; there is no mdw or user permissions. Thanks

Joan,
I think we finally fixed the issue. We had one machine that I didn't know a
user was using that did not have the latest office sp installed. Once I
installed that, everything seemed to work as it did before. I wonder if the
problem occurred when the other machines were updated. Thanks again for your
help.

 

[Joan Wild]

Glad it got sorted. 2000 was/is flakey, so keeping everyone up-to-date is
important as it is sensitive.

Since everyone is on 2003, I'd be inclined to convert the mdb to 2003
format, but if things are working....

 

[jnlewis]

I'm having the same problem. I have a split shared dateabase with 5 users.
After a recent server restore, 3 front end users log in with no problem. 2
users can only log in with read only permissions. The folder gives them the
correct permissions.
I've backed up the database, tried to run debug, compile on the front end,
but nothing came up after hitting compile.