Shared vs Exclusive

[JHC]

I am confused about Shared or Exclusive default open mode. I have a data
base on a shared server. User 1 opens the DB and the options show Exclusive.
User 2 opens the same DB from the same server and sees that the options show
Shared? How can this be? Neither user was prevented from making changes to
the same tables.

The Opend databases using record-level locking box is checked and the
Default record locking is set to Edited record.

What is the recommended setting for Default open mode?

How does one put the database in exclusive mode? When administrative
functions need to happen, is there a way to keep other users from the data?

 

[Ken Sheridan]

Is the database split into front and back ends? If each user has a copy of
the front end containing the forms, reports, queries etc on their local
machine, which is normal practice and the most efficient modus operandi,
while the back end containing the tables is on the server, then it is the
front end which is being opened exclusively or shared, not the back end,
which would account for the differences.

The default open mode for the back end file should certainly be Shared. It
probably doesn't make a lot of difference in practice whether the front end
is opened in shared or exclusive mode as only one user will be using each
instance of it at any one time.

For maintenance purposes the back end can be opened exclusively through the
normal common dialogue via the Windows interface. Maintenance of the front
end on the other hand would be done on a development copy of the front end
file. The updated file would then be copied to each user's local machine.
This can be automated over the network.

Pessimistic locking (Edited Record) is nowadays favoured. In the past
Optimistic locking (No Locks) used to be preferred as Access previously
locked the page, not the record per se, so several records could be locked at
any one time, thus needlessly preventing users from editing data until the
page was unlocked. With the introduction of true record locking this
objection to pessimistic locking s no longer relevant.

Ken Sheridan
Stafford, England

 

[JHC]

Thanks for the information Ken. Sorry for the long delay. I was sick and
then my hard drive failed. It took a while to get things back together.

I did not set the Front-End / Back-End environment. I just have the one
file with the tables, forms, etc. in the same file. There are only 5 users
of the application, and rarely more than 1 or 2 concurrent users. But you
never know.

I tried to split the tables from the UI. I put them both on the server, but
performance was atrocious. If I were to split the application again, and
distribute the client side, I am not sure how I would ensure that the client
is the correct version.

So, my question still stands. If 2 people open the same file from the
server, why does one see shared and the other exclusive? Or do I need to
switch to the FE-BE approach and if so, how does on ensure the users all have
the same version of the UI component?

-John

 

[Douglas J. Steele]

The permissions that each user has on the folder where the MDB file exists
can cause the situation you're describing. I've heard of cases where each
user has the ability to create files in the folder (which is required in
order for Access to be able to create the locking LDB file when the first
user opens the database), but not to write to files that others have written
in that folder (which means that they're locked out if the LDB file already
exists).


One way to ensure that everyone has the correct version of the front-end is
to use the free Auto FE Updater Tony Toews has at
http://www.granite.ab.ca/access/autofe.htm He also has some suggestions for
how to improve performance of a split application on his site.