Microsoft KB Archive/109740

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<math display="inline">, ByVal lpKeyName</math>, ByVal lpDefault<math display="inline">,_ ByVal lpReturnedString</math>, ByVal nSize%, ByVal lpFileName$)

2. Add the following function:

   Function Which_Users () Dim SqlStr<math display="inline">, GroupName</math>, Message<math display="inline">, SysDB</math> 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.