INF: How to List Users in a Given Group
  
PSS ID Number: Q109740
Article last modified on 06-08-1995
 
1.00 1.10
 
WINDOWS
 

---------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Access versions 1.0 and 1.1
---------------------------------------------------------------------
 
SUMMARY
=======
 
This article describes a user-defined Access Basic function you can use to
list users belonging to a given group in your Microsoft Access system.
 
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
================
 
The following example demonstrates how to create and use the sample
function Which_Users() to list the users in a group:
 
1. Create a new module and add the following lines to the Declarations
   section.
 
   NOTE: In the following sample code, an underscore (_) is used as a line-
   continuation character. Remove the underscore from the end of the line
   when re-creating this code in Access Basic.
 
      Option Explicit
      Declare Function GetPrivateProfileString% Lib "Kernel"_
         (ByVal lpApplicationName$, ByVal lpKeyName$, ByVal lpDefault$,_
         ByVal lpReturnedString$, ByVal nSize%, ByVal lpFileName$)
 
2. Add the following function:
 
      Function Which_Users ()
         Dim SqlStr$, GroupName$, Message$, SysDB$
         Dim lpReturnedString$, nSize%, GetInfo%
         Dim MyDB As Database, MyDyna As Dynaset
         Dim MySnap As Snapshot
 
         On Error GoTo Empty
 
         GroupName$ = InputBox("Please Enter a Group Name", "Group")
         If GroupName$ = "" Then
            MsgBox "You Must Enter a Name", 16, "Error"
            Exit Function
         End If
 
         lpReturnedString$ = Space$(255)
         nSize% = Len(lpReturnedString$)
         GetInfo% = GetPrivateProfileString("Options", "SystemDB", "", _
            lpReturnedString$, nSize%, "MSACCESS.INI")
         SysDB$ = lpReturnedString$
 
         Message$ = UCase(GroupName$) & " Has the Following Members:" &_
            Chr(13) & Chr(10)
 
         Set MyDB = OpenDatabase(SysDB$)
         Set MySnap = MyDB.CreateSnapshot("MSysAccounts")
 
         SqlStr$ = "[Name]= '" & GroupName$ & "'"
         MySnap.FindFirst SqlStr$
         If MySnap.NoMatch Then
            MsgBox UCase(GroupName$) & " Is Not A Defined Group"
            MySnap.Close
            MyDB.Close
            Exit Function
         Else
            MySnap.Close
         End If
 
         SqlStr$ = "SELECT MSysAccounts.Name FROM MSysAccounts AS B,_
            MSysGroups, MSysAccounts, "
         SqlStr$ = SqlStr$ & "B INNER JOIN MSysGroups ON B.SID =_
            MSysGroups.GroupSID, "
         SqlStr$ = SqlStr$ & "MSysGroups INNER JOIN MSysAccounts ON_
            MSysGroups.UserSID = MSysAccounts.SID "
         SqlStr$ = SqlStr$ & "WHERE ((B.Name= '" & GroupName$ & "'));"
 
         Set MyDyna = MyDB.CreateDynaset(SqlStr$)
 
         If MyDyna.RecordCount = 0 Then
            MsgBox UCase(GroupName$) & " Has No Members"
            MyDyna.Close
            MyDB.Close
            Exit Function
         End If
 
         MyDyna.MoveFirst
         Do While Not MyDyna.eof
            Message$ = Message$ & Chr(13) & Chr(10) & MyDyna.[Name]
            MyDyna.MoveNext
         Loop
         MsgBox Message$
         MyDyna.Close
         MyDB.Close
         Exit Function
 
      Empty:
         If Err = 3021 Then
         MsgBox UCase(GroupName$) & " Is not a Valid Group", 16, "Error"
         Else
            MsgBox "An Unexpected Error (#" & Err & ") Occurred:" & _
               Chr(13) & Chr(10) & Error(Err), 16, "Error"
         End If
         Exit Function
         Resume
      End Function
 
3. From the View menu, choose Immediate Window.
 
4. Type the following line in the Immediate window and press ENTER:
 
      ?Which_Users()
 
REFERENCES
==========
 
Microsoft Access "User's Guide," version 1.0, Chapter 25, "Administering a
Database System," pages 612-614, version 1.1, pages 616-618
 
For more information on using external functions, search for "declare
external procedure" then "Declare Statement" using the Microsoft Access
Help menu.
 
Additional reference words: 1.00 1.10
KBCategory: kbprg
KBSubcategory: PgmApi
=============================================================================
Copyright Microsoft Corporation 1995.
