Security in Multi User env

[Antonio]

I have a database that resided on a central server, while
accessed by 18 different workstations within my
department. Recently, we have encountered several
problems with the database and the general thought is
that we need to split the database into a front and back
end to resolve the issues. However, I used the User Level
Security Wizard to filter the access permissions among
the 30 different members of the department. (supervisors,
team leaders, users, etc). Now I need to split the
database, but I am unsure of how security will run. I
have to give a copy of the front end to each of the work
stations, but does that include the mdw file as well?
Before the MDW file sat in the same folder as the
database file. Do I leave it with the Back End copy of
the database? Or do I place it on everyone's computer
with the front end copy?? Would it be easier to run the
User Level Security Wizard on the Front End (assuming I
import the tables into a new database, and the quiries,
forms, rpts, modules, macros into another) and then
secure the back end with a generic password? I just dont
know how to proceed.
TIA

 

[TC]

Regardless of how you proceed, this is what you need to achieve.

- db split into a BE with all of the tables (but nothing else), FE with all
the other objects (queries, forms etc.) & links to the actual tables in the
BE.

- one copy of the BE, for all users, on the server;

- one copy of the FE for >each user<; the obvious way to achieve this is to
have a copy on each user's PC; if you make any FE changes, you must roll
those out to each user's copy;

- a copy of the wgf on each user's PC, or, a single copy (for all users) on
the server. A disadvantage of having a seperate copy for each user, is that
if you make any workgroup changes, you must roll them out to each PC.

- each user starts the db using a shortcut of the following format, which
speifically identifies the db & workgroup files to use:

(all on one line)

"full path to MSACCESS.EXE"
"full path to MDB or MDE"
/wrkgrp "full path to MDW"

HTH,
TC

 

[Antonio]

Thank you so much for responding. I have one question,
does splitting the database into front and back end
increase the amount of time forms open/code runs?
Ideally, I think I would like to have the back end on the
server, as well as the security file (mdw, right?) and
then the front end and its short cut on each
computer...but does setting it up this way cause the
front end to "work" harder and thus increase the amount
of time it takes for forms to open (I tried this and a
form that takes 5 seconds to open, the word "calculating"
sits in the lower left hand corner of the screen for 5
seconds before the form opens, took 25 seconds to open)?
Is there a way to speed up the forms/code that runs for a
particular database?
Thanks again

 

[TC]

Thank you so much for responding.

No probs.

I have one question,
does splitting the database into front and back end
increase the amount of time forms open/code runs?

Not if the BE is on the same PC. But often >yes<, if the BE is on a
different PC.

For example, say you have an unbound switchboard form. No tables, queries or
recordsets are opened by that form. At this stage, Access (really, Jet) does
not need a locking (LDB) file for the back-end database. Say you now select
a switchboard option that displays a bound form. At this point, Jet must
create a locking (LDB) file for the back-end database. Then, when you close
the bound form & return to the unbound switchboard, Jet deletes the locking
file. This continual creation & deletion of locking files, can have a
significant impact on system responsiveness. A similar thing occurs if you
relink the back-end tables programatically - this can go from a few seconds
(with the BE on the same PC), to several minutes (with the BE on a different
PC).

Luckily, the solution to this problem is simple. Just open a "manual"
connection to the BE database, & keep that open for the duration of the
whole run. The relevent code is shown below. gDB must stay "in scope" for
the whole duration of the run. You must place the code accordingly. For
example, if your unbound switchboard form is always open (just hidden or
overlayed when it calls other forms or reports), you could put the first
line in Form_Open of the switchboard form, and the second in Form_Close:

when system opens:
dim gBE as database
set gBE = dbengine.opendatabase("full path to BE database")

when system closes:
gBE.close
set gBE = nothing

Ideally, I think I would like to have the back end on the
server, as well as the security file (mdw, right?) and
then the front end and its short cut on each
computer...
Yes.


but does setting it up this way cause the
front end to "work" harder and thus increase the amount
of time it takes for forms to open (I tried this and a
form that takes 5 seconds to open, the word "calculating"
sits in the lower left hand corner of the screen for 5
seconds before the form opens, took 25 seconds to open)?
Is there a way to speed up the forms/code that runs for a
particular database?

See above. Another issue is that you may need to redesign your forms etc. to
pull the minimum data, over the network. For example, a bound form that
opens the whole table may be quick when the BE is on the same PC, but slow
when the BE is on a different PC. Common advice is to have the form open the
minimum set of records required. Similar with bound list/combo boxes etc.

There's some other stuff here. (But note you do not need to open a bound
form to make a persistent connection. All you need is the code that I have
shown above.)
http://www.granite.ab.ca/access/performancefaq.htm

HTH,
TC