Access on a Network - Cannot open file - Opened exclusively by an.

[Guest]

Access 2002 & 2003 database on a network of 20 users. Tables are on one
machine acting as a server. Database on each user machine with linked tables
to the server. Sometimes a user when opening database gets the following
message. "The Microsoft Jet database engine cannot open the file "\\ path &
file name for linked tables" It is already opened exclusively by another
user, or you need permission to view it's data". On some occasions an
additional footnote says that access is denied because the maximum number of
users allowed has been reached.
Is there a limit to the number of users accessing a database in this
configuration??
How can this problem be solved??

 

[Joseph Meehan]

Access 2002 & 2003 database on a network of 20 users. Tables are on one
machine acting as a server. Database on each user machine with linked
tables
to the server. Sometimes a user when opening database gets the following
message. "The Microsoft Jet database engine cannot open the file "\\ path
&
file name for linked tables" It is already opened exclusively by another
user, or you need permission to view it's data". On some occasions an
additional footnote says that access is denied because the maximum number
of
users allowed has been reached.
Is there a limit to the number of users accessing a database in this
configuration??
How can this problem be solved??

There is an option to open a database exclusively in order to work on it
and keep others out while you are doing so. Sometimes someone will set
their copy of access to do this all the time. You may need to find that
someone. It is also possible that you are experiencing some network
problems including individual users access right to the folder where the
Access database is located. It also happens that from time to time the
NAMEOFFILE.LDB file (that is the locking database file) did not get deleted
when the last person using it quit. In this kind of situation it often
happens when someone shuts off their machine without fully exiting Access.

I suggest that it sounds like you are ready for two big changes in your
Access life.

# 1. When you have 20 people on the same database you really need to
have that database split. This puts all the data that may be changed on the
network and puts all the static data, forms, reports and queries on a
separate database on each user's machine. The local (called front end)
programs "link" to the data on the remote (called back end) database for the
data. This set up will work faster and with a lot less problem.

#2 User level security. It helps keep an individual user from making
too many big errors. It goes a long way to protecting all that work.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

 

[Tony Toews]

Access 2002 & 2003 database on a network of 20 users. Tables are on one
machine acting as a server. Database on each user machine with linked tables
to the server. Sometimes a user when opening database gets the following
message. "The Microsoft Jet database engine cannot open the file "\\ path &
file name for linked tables" It is already opened exclusively by another
user, or you need permission to view it's data".

This could be a permissions problem.

This is probably a permissions problem on the directory in which the
backend is installed. The users must have create/delete privileges
to that directory. What is happening is that Access can't create the
..ldb file which allows multiple users to update the MDB. So Access
only allows one user at a time.

One simple way of testing this is to ensure the users can create and
delete a file in the network share. Any file, even using notepad, is
enough to test this. I simply don't trust all those permissions screen
within the OS. You never quite know whats lurking behind the advanced
button. So test this yourself.

Sometimes it could only be one user who does not have create
privileges to cause problems who just happens to be the first user
into the MDB at that moment in time. Then all the other Access users
can't access the file because the first user is in exclusively.

"I found that I could keep the permissions set to Change, but had to
ensure that the directory in which the db resides was set to not
inherit permissions from it's parent. It seemed that every time a new
user logged onto a given machine, it got messed up."

See ACC: Determining Which User Has Opened Database Exclusively
(Q169648)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q169648
Essentially you must use tools on the server to determine who has
locked the file.

For MS info see ACC: Introduction to .ldb Files (95/97) [Q136128]. Or
the version appropriate for your version of Access.

On some occasions an
additional footnote says that access is denied because the maximum number of
users allowed has been reached.
Is there a limit to the number of users accessing a database in this
configuration??

The additional footnote sounds like you're hitting the maximum of ten
connections allowed on a Windows non server OS. Thus you need to
consider installing a server OS such as Windows 2003 Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm