Force users out of database

[mc77]

I am the database manager for a department, not IS, and have created a new
database to track patient insurance appeals. The database is saved in a
shared directory and is used by about 10 people in different departments.
Often times I need to make a change to a form or report but cannot because
someone has left the database open and walked away from their desk. This
costs me valuable time waiting for them to return so I can have everyone
close the file. I have fairly basic programming experience with Access but
have learned a lot in building this new database. Is there a way to force
these users out so that I can make my changes?

 

[Klatuu]

Ok, lets cover some of the basics of a multi user application.
You say the database is on a shared folder, but it is not clear whether your
database is split or not. In a multi user environment, it is very important
that it be split. If it is not, you run into problems not only with
updating, but with corruption and performance problems.

The correct configuration is to have the data (tables and relatiionships) in
one mdb. This is called the Back End (BE). The application (forms, reports,
queries, modules, macros) should be in another mdb. This is called the Front
ENd (FE). Each user should have a copy of the FE on their own computer. It
should be linked to the data in the BE using network paths rather than drive
mapping. That is the path should be something like
\\MyServer\MyDataFolder\MyBE.mdb rather than K:\SomeFolder\MyBE.mdb. The
reason for this is that not all users will necessarily have the same drive
mapping.

Now once that environment is set up correctly, you can not only boot users
out of the system, you can manage deploying updates to the application.

The concept for kicking people out is to open a form when the app launches.
This form should be hidden and stay open at all times. Put a timer event in
the form that checks the value of a field in a table when the timer event
fires. If the value in the field matches what you define as a flag to stop,
then close the application.

I like to warn the user that the system is coming down after a period of
time to give them time to find a good stopping point.

Another thing you can do in that form is to apply your modification and
update each user's copy of the FE. That is a matter of having a static value
in the FE that you compare to a value in the BE that compares the running
version to the current version. If the running version is older than the
current version, you can use an update utility to close the app and copy the
new version from a designated shared folder and restart it. Tony Toews has a
good one on his web site and there are others floating around.

 

[mc77]

I guess I need to first figure out how to split the database. As I said, I
am not IS nor do I have a programming background. Everything I have learned
about Access programming I have taught myself. Right now, the database is
simply a file saved in the departmental shared directory. One other employee
and myself are responsbile for all of the data entry. The other users each
have their own logins with forms that give them access to only what they need
to view, they are not making any changes or additions.

 

[David H]

Its actually very straight forward.

http://support.microsoft.com/kb/304932

 

[David H]

Something else, if your users only need read-only access, you may want to
consider making the data available via the INTRAnet using ASP.NET. Visual Web
Developer Express is available as a free download from Microsoft and has
enough tools to get you started. A lot of it is drag and drop so it shouldn't
be too difficult to build a connection to the Access database and create
simple web pages. However, to make it available on the INTRAnet, you'll need
some minor help from IT. At any rate you should be able to get a good look
and feel from just running Visual Web Developer on your own machine.