What's the best way to secure my database?

[steve goodrich]

I have a database in access 97 that is used to book visitors into our
building. There are over 100 people allowed to book the visitors in

There are 22 different access levels that the visitors are allowed to enter
depending who they are visiting.



There are two combo boxes on the form "Auth By" and "Levels"

The person booking in the visitor would click on the "Auth By" combo box,
select their name, then click the "Levels" combo box and select the
appropriate levels.



It all works great but there is no security. Basically any one of the people
can click anyone's name and assign any levels.



Will the help of you guys I've managed to limit the options in the levels
field depending on the choice in the "Auth By" field, but anyone can still
click anyone's name.



What's they best way to secure this database.



It's just one file on the network at the moment.



I've been advised to split it into a front and back end. If I do this would
I be able to secure it with user permissions.



There is only the one form and a few queries run by command buttons.



Any help would be much appreciated



Steve

 

[Guest]

I have a database in access 97 that is used to book visitors into our
building. There are over 100 people allowed to book the visitors in

There are 22 different access levels that the visitors are allowed to enter
depending who they are visiting.



There are two combo boxes on the form "Auth By" and "Levels"

The person booking in the visitor would click on the "Auth By" combo box,
select their name, then click the "Levels" combo box and select the
appropriate levels.



It all works great but there is no security. Basically any one of the people
can click anyone's name and assign any levels.



Will the help of you guys I've managed to limit the options in the levels
field depending on the choice in the "Auth By" field, but anyone can still
click anyone's name.



What's they best way to secure this database.



It's just one file on the network at the moment.



I've been advised to split it into a front and back end. If I do this would
I be able to secure it with user permissions.



There is only the one form and a few queries run by command buttons.



Any help would be much appreciated



Steve


I don't know about Access 97 but this may help:

http://www.jmwild.com/security02.htm

 

[Joan Wild]

You could implement security (see www.jmwild.com/AccessSecurity.htm) and
then use the CurrentUser() function to set the Auth By field. The
CurrentUser function returns the Access user login name.

If you don't want to implement security, you can use the code at
http://www.mvps.org/access/api/api0008.htm to grab the user's network login
name and set the field with that.

You wouldn't provide a combobox for the AuthBy field; set it and don't allow
edits on the form.

 

[steve goodrich]

Joan
I've been trying to follow your instructions to secure my access 97 db
I'm not sure how to create the shortcut
path to msaccess.exe" "path to mdb" /wrkgrp "path to secure mdw"

If access is in c:\program files\microsoft office and my database is
c:\visitors access diary and my wrkgrp is c:\stevewg.mdw
What exactly do I type in the shortcut
thanks for your help
regards
Steve

 

[John W. Vinson]

If access is in c:\program files\microsoft office and my database is
c:\visitors access diary and my wrkgrp is c:\stevewg.mdw
What exactly do I type in the shortcut

All on one line:

"C:\Program Files\Microsoft Office\msaccess.exe" "C:\visitors access diary"
/wrkgrp "C:\stevewg.mdw"

Note that by default msaccess.exe is NOT in that location - it's typically in
a subfolder of the \Microsoft Office\ folder; and it would be very unusual,
and probably unwise, to store your databases and mdw files in the C: root
directory.

John W. Vinson [MVP]

 

[steve goodrich]

Thanks for the info John.
I placed them on the root drive so the path would be short, so now I know
how to enter the shortcut I can locate them anywhere.
Where would you suggest I locate them.
The db is on one of our networks at the moment. I just copied it to my own
pc to practice on.
Regards
Steve

 

[Joan Wild]

First of all I strongly suggest you split the mdb; since it's secured, don't
use the splitter wizard - do it manually.
www.jmwild.com/SplitSecure.htm
You place the backend on the server along with the secure mdw. Give all
users modify permission on the windows folder where these files are placed.

You give a copy of the frontend to each user. The frontend will contain
linked tables, linked to the backend on the server (when you create the
linked tables, you can use My Network Places to locate the backend so that
it uses UNC pathnames; that way you don't need to worry about each user's
drive mappings).

You also give each user a desktop shortcut. If everyone has Access
installed in the same place and the frontend is put in the same place on
their workstation, then you can create the shortcut and copy it to them
(it's just a file with a lnk extension).
"c:\Program Files\Microsoft Office\Office\msaccess.exe"
"C:\visitors access diary.mdb"
/wrkgrp "\\servername\share\stevewg.mdw"

That's all on one line. The \\servername\share\path is the UNC path to the
mdw on the server.

 

[John W. Vinson]

Thanks for the info John.
I placed them on the root drive so the path would be short, so now I know
how to enter the shortcut I can locate them anywhere.
Where would you suggest I locate them.
The db is on one of our networks at the moment. I just copied it to my own
pc to practice on.

Depends entirely on your requirements (and those of your network
administrator).

A shared database should certainly be split: a "backend" containing only the
tables on a shared folder accessible to all users, and a "frontend" containing
links to the tables, forms, reports and code; each user would get their own
copy of the frontend.

John W. Vinson [MVP]

 

[John W. Vinson]

First of all I strongly suggest you split the mdb; since it's secured, don't
use the splitter wizard - do it manually.
www.jmwild.com/SplitSecure.htm

Thanks, Joan - I'd missed this resource. Noted and bookmarked!

John W. Vinson [MVP]