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:
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$)
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
From the View menu, choose Immediate Window.
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.