Users on a Multi-User DB

Masters of Microsoft Access,

Before I explain what I am struck on, I will explain the DB layout. My DB
has been split into Backend and Front-end. And each User Group has there own
DB front-end, where only the tables needed have been linked.

I am about to add User-Level Security using the wizard, but after testing
them I am not sure if any does the following:

 > Allow access to FORMS and REPORTS ONLY. And able to Add New and Amend
old records

 > NO ONE apart from Admin, is able to add tables or edit or delete table
structures.

 > NO ONE apart from Sales is able to add/edit/delete queries.

 > NO ONE apart from Admin and Sales is allow to view the queries and Table
structures, only the forms/report which are using the tables.

 > Show WHICH user is currently using the system and to log this information.

Thanks in Advance. I would be glad from any help you can provide.

Cowji

"Cowji" wrote in message

 > Masters of Microsoft Access,
 >
 > Before I explain what I am struck on, I will explain the DB layout. My DB
 > has been split into Backend and Front-end. And each User Group has there
 > own
 > DB front-end, where only the tables needed have been linked.
 >
 > I am about to add User-Level Security using the wizard, but after testing
 > them I am not sure if any does the following:
 >
  >> Allow access to FORMS and REPORTS ONLY. And able to Add New and Amend
 > old records

You don't need to use any security, or even any code for that matter to keep
users out of the ms-access part. The designers of ms-access (thoughtfully)
gave you a set of "options" that you set in tools->startup. Using
tools->startup you can hide all of the ms-access interface, and also keep
people out of things they are not supposed to see. Things like what form
shows at startup, and setting/hiding of the built-in menus can be set here.

To see how (or what settings) you need, then try downloading the 3rd example
here (ms-access interface hidden)

<a rel="nofollow" style='text-decoration: none;' href="http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm" target="_blank">http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm</a>

 >
  >> NO ONE apart from Admin, is able to add tables or edit or delete table
 > structures.

If you use the above step #1 I outlined, then only YOU the developer will
be able to get inside to see the table stuff.

You have obviously discovered as a developer the ONLY sane and
competent way to develop in ms-access is to work with a split database.


  >> NO ONE apart from Sales is able to add/edit/delete queries.

Boy, the above is a formula for disaster. You mean you are going to force
your users to go out and learn SQL? You mean, you are going to allow users
to actually mess with, and modify existing parts of your application?
Allowing
users to do this is a mistake, and is too costly. I been
writing and distributing commercial software for 20 years, and I have NEVER
EVER had to distribute ANY software where the end users actually need, (or
are allowed) to modify the queries. You need to re-think some of your
designs here, as having end users have to go in and modify sql in your
application is defiantly not the way to go here. If you need to change some
parameters or conditions for a report, then the simply approach is to use
the
reports "where" clause, and thus you don't need to modify the sql for
reports. You can seem some screen shots here of some report prompt screens
that use the "where" clause of openreprot here.

<a rel="nofollow" style='text-decoration: none;' href="http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html" target="_blank">http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html</a>

I have many applications that have been on clients sites for 10+ years, and
they NEVER have asked for a new report due to the above approach.

 >
  >> NO ONE apart from Admin and Sales is allow to view the queries and Table
 > structures, only the forms/report which are using the tables.

You can accomplish the above, and use security to do this. As mentioned, to
prevent, and hide all of ms-access, you don't need any code, nor need to
setup security. However, to do the above, and allow *some* users to modify
table and designs..and others not to, you would use security.

In general, from a developers point of view, you work with a split system
(you mention you are now doing this), it is the developer who changes this
stuff, and not users. So, EACH workstation gets a copy of the users
(production) version of your software (and, preferable this is a mde). While
users are working and using this software, you of course are working on the
next great version for release (the mdb). As a general rule, thus you don't
allow, or have users touching the production version of your software.
And, of couse, this approach means you really don't have to bother
setting up secirty, as no one except the develoepr will be able to
modify things.

 >
  >> Show WHICH user is currently using the system and to log this
  >> information.

Logging what the user does, and tracking what featrues they use has NOTHING
to do with secirty. You can most certanly write some code, and track users
as to what they do. I have a post at JoelOnSoftware that talks about making
a logging system (this example was in ms-access).

<a rel="nofollow" style='text-decoration: none;' href="http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=8341&ix" target="_blank">http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=8341&ix</a>

Make sure you read all of the respnoes, as I give more details about logging
in the above as a response to questions. The above was done in ms-access.

As for showing who is in the database at all times? You can use:

<a rel="nofollow" style='text-decoration: none;' href="http://www.mvps.org/access/general/gen0034.htm" target="_blank">http://www.mvps.org/access/general/gen0034.htm</a>

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal DeleteThis @msn.com
<a rel="nofollow" style='text-decoration: none;' href="http://www.members.shaw.ca/AlbertKallal" target="_blank">http://www.members.shaw.ca/AlbertKallal</a>