INF: How to Find Which Groups a User Belongs To
  
PSS ID Number: Q106297
Article last modified on 10-04-1994
 
1.00 1.10
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------
 
SUMMARY
=======
 
Microsoft Access has no built-in mechanism for finding which groups a user
belongs to. This article demonstrates a sample Access Basic function that
you can use to check if a user is a member of a specific group.
 
Notes
-----
 
 - The technique described below relies on the use of tables stored with
   your SYSTEM.MDA file. These tables are undocumented and are subject to
   change in future versions of Microsoft Access. Use of the system tables
   is not supported by Microsoft.
 
 - This article assumes that you are familiar with Access Basic and with
   creating Microsoft Access applications using the programming tools
   provided with Microsoft Access. For more information on Access Basic,
   please refer to the "Introduction to Programming" manual.
 
MORE INFORMATION
================
 
You can use the following sample function to check if a user belongs to a
specific group:
 
NOTE: In the following sample code, an underscore (_) is used as a line-
continuation character. Remove the underscore when re-creating this code in
Access Basic.
 
   '---------------------------------------
   'GLOBAL DECLARATIONS SECTION
   '---------------------------------------
   Option Explicit
 
   '---------------------------------------
   'Function: ug_Group()
   '---------------------------------------
   Function ug_Group (UserName As String, CheckGroup As Variant)
      Dim MyDB As Database, MyQueryDef As QueryDef, MySnap As Snapshot
 
      'Trap Error
       On Error GoTo ug_Group_Err
 
      'Verify CheckGroup is String or Null
       If VarType(CheckGroup) <> 1 And VarType(CheckGroup) <> 8 Then
         ug_Group = Null
         Exit Function
       End If
 
       Set MyDB = OpenDatabase("SYSTEM.MDA")
       Set MyQueryDef = MyDB.OpenQueryDef("MSysUserMemberships")
       MyQueryDef![UserName] = UserName
       Set MySnap = MyQueryDef.CreateSnapshot()
 
       'Check if CheckGroup = Null or String
       If IsNull(CheckGroup) Then
           If MySnap.BOF Then
               ug_Group = 0
           Else
               MySnap.MoveLast
               ug_Group = MySnap.RecordCount
           End If
       Else
             MySnap.FindFirst "[Name]='" & CheckGroup & "'"
             ug_Group = IIf(MySnap.NoMatch, False, True)
       End If
 
       MySnap.Close
       MyQueryDef.Close
       MyDB.Close
       Exit Function
 
   ug_Group_Err:
       ug_Group = "Error: " & Error
       Exit Function
 
   End Function
 
You can use the function ug_Group() to check if a user belongs to a
specific group, or to determine the total number of groups to which the
user belongs. You can specify any user in the SYSTEM.MDA file, or you can
dynamically determine the current user of the application.
 
The ug_Group() function uses the following arguments:
 
   Argument     Description
   ----------------------------------------------------------
   UserName     String expression of the user to be
                checked. Use the User() function to pass the
                current user.
 
   CheckGroup   Variant expression of the group to check
                if UserName is a member. A group name
                is passed as a String expression.
                Optionally, this value can be passed as null.
 
If the argument CheckGroup is a String, the function will return true (-1)
if the UserName is a member of CheckGroup, or will return false if the
UserName is not a member of CheckGroup.
 
If the argument CheckGroup is null, the function will return the number of
groups of which UserName is a member. If the UserName has not been defined
as a user in the SYSTEM.MDA file, the value 0 will be returned. Since all
users are members of the group Users, the function will return the value 1
if UserName is not a member of any other groups. The function will return a
value greater than 1 if UserName is a member of other groups. The value
returned will equal the total number of groups of which UserName is a
member.
 
If the argument CheckGroup is not a String or null, the function will
return null.
 
If an error occurs within the function, a description of the error will be
returned.
 
For example, assume the following users and groups have been created:
 
   Users: Donald, Jeanie, and Chelsea
   Groups: People and Pets
 
   Donald is a member of the groups People and Users.
   Jeanie is a member of the groups People and Users.
   Chelsea is a member of the groups People, Pets, Users, and Admins.
 
Also assume that Chelsea is currently logged into Microsoft Access.
 
Type the following examples in an Immediate window and note the results.
Remember that -1 equals true and 0 equals false:
 
   Example:    ? ug_Group( User(), "Admins" )
   Response:   -1
   Meaning:    Chelsea is a member of Admins.
 
   Example:    ? ug_Group( "Donald", "Pets" )
   Response:   0
   Meaning:    Donald is not a member of Pets.
 
   Example:    ? ug_Group( "Jeanie", NULL)
   Response:   2
   Meaning:    Jeanie is a member of two groups.
 
REFERENCES
==========
 
Microsoft Access "User's Guide," Chapter 25
 
Microsoft Access "Language Reference," page 489
 
Additional reference words: 1.00 1.10 ADK
KBCategory: kbusage
KBSubcategory: ScrtOthr
=============================================================================
Copyright Microsoft Corporation 1994.
