FrontEnds with "filtered" access to records in backend tables

[LBRT]

I have a split database with several tables, queries, forms, macros - what is
the quickest way to give about 30 users separate front ends that restrict
about 4/5 of them access only to the records for their own locations, the
other 1/5 can see all records?

The db (A2002-3, also opens in A2007) tables contain mostly kid demographic
information, annual test scores, and ongoing weekly scores on progress tests
in separate math and reading skills. There are 6 campuses and central staff.
I want users at school A only to see and edit records for kids in school A,
same for the other schools, but central staff can see all records.

Is there a more global way to apply a filter to all the tables based on
school ID, rather than modify every query, report, macro, and form for each
location?

Thank you -

LBRT

 

[Ken Sheridan]

You'll either need to give each user a front end in which they have access to
the data via queries which restrict them to the rows in question by criteria
on the 'school' column, or by queries which restrict rows on the basis of the
user's log-in name. Simply restricting on the basis of the 'school' column
would of course restrict access on the basis of the front end in use, not
necessarily the user, so the latter is better. You can get the current user
name with the following module:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

Just call the GetUser() function to get the name. Each school can then be
associated with a set of user names by means of a related table. If you've
implemented Access's (pre-2007) user and group security then you van of
course get the current user's Access user name with the CurrentUser()
function rather than calling the Windows API.

For a more flexible approach you might like to take a look at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24069


It demonstrates how to implement row level security based on groups. It
doesn't use real user and group security, just simulates it for demonstration
of the methodology.

Ken Sheridan
Stafford, England

 

[LBRT]

Most excellent! Thanks so much for your quick and thorough answer - I will
look at the alternative, too - I appreciate the help a lot! Have a great week!

LBRT