js said:
Recently we had Access 2003 installed at our workplace. We have a
database, located on a server, that is used by multiple employess.
We have placed shortcuts on the desktops of the employees, however I
have found that the shortcut will only work when there are no other
people using the database. When there are multiple people logged into
the database you must open Access and navigate to the database using
"open file", the desktop shortcut does not work.
Does anyone know about this and know of a possible fix to this
problem??
Thanks in advance for any help.
Access is be default multi-user, but you can open in it "exclusive"
mode. I would guess those shortcuts have a switch to open it in
exclusive
mode.
However if you have multiple people accessing a database, you would
be
wise to split the database so each user has their own front end of the
system and the data is all stored in the back end on the server. The
front
ends include all the forms, reports etc, except for the shared data.
This
reduces the network traffic, speeds things up and reduces the chances for
errors.
What parts of the database will be on the "server" and will be
called
the Back end database from now on and which parts will be on each user's
machine and will be called the front ends. The back end should hold all
data that is shared and may be changed by the users. It should also
contain
all or most data that more than one user will need access to and may be
changed by you from time to time. Most other data that does not change
or
that will only be used by that particular user should be on the Back end
databases on the users machines.
For example you may have all the sales made by a unit on the back end
along with the price list. The sales may been to be shared by everyone
so
they all know what has been done or pending. The price list may not be a
field they will change, but you may need to change to assure everyone has
the same current price available.
Each individual machine may have something about your company like
addresses that does not change or even product descriptions etc. You may
want each user to be able to store personal information about customers
like
their kids names or shared information about sports teams or you may want
to
put this on the server so everyone will have this information.
This is an art form and a science to get this part of the planning
designed and will be an ongoing job and should include the users in the
planning.
Access works best if it does not need to move a lot of information
over
the LAN which means static data is best kept on the front end databases.
Also kept on the front end machines will be most forms, reports queries
etc.
This will allow the whole system to work faster and in some cases allow
for
customization of some forms reports etc.
This may seem like a lot of work and off the point of the question
you
were asking, but it is very important that this part of the job be done
first and right.
Next is the mechanics of setting up the back end on the server,
dumping
in the data and putting the front end copies on each user's machines and
assuring that the links work. Access has a built in database splitter
that
may make this part of the job (moving from a single database with all the
data and forms etc. to two databases a front end and a back end.) easier.
Look under the Tools menu for it.
You may also want to look into user level security to protect the
database and data before you finish.
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 easily
lock
you out of your database, which might require the paid services of a
professional to help you get back in.
Practice on some copies to make sure you know what you are doing.
Splitting a database can be a big job, but done right everyone will
thank you and wonder how they did their jobs without it.
Note: back ups become more important here. If you LAN does not support
automatic backups you should provide a method of backing up the data,
even
if that means you do it manually.
