ACC2: Only Admins Group Members Can List Groups They Belong To

• Microsoft Access version 2.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

You can use Access Basic code to determine to which groups a user belongs, but only members of the Admins group can view this information. For example, running the following sample function will return the current user's name and a list of the groups to which the current user belongs:

   Function GetGroups ()
   Dim W As WorkSpace, US As String, U As User, i As Integer, j As Integer

      Set W = DBEngine.Workspaces(0)
      US = CurrentUser()
      Set U = W.Users(US)

      Debug.Print U.Name
      For j = 0 To U.Groups.Count - 1
         Debug.Print "   " & U(j).Name
      Next
   End Function

   Record(s) can't be read; no Read Data permissions on 'MSysGroups'

RESOLUTION

To work around this behavior, you must log in to a new workspace as a member of the Admins group and pass the name of the user whose groups you want to list. The following sample function demonstrates this method:

   Function GetGroups2 ()
      Dim W As WorkSpace, US As String, U As User, i As Integer

      Set W = DBEngine.CreateWorkspace("NewWS", "MyAdmin", "MyPassword")
      US = CurrentUser()
      Set U = W.users(US)

      Debug.Print U.Name
      For i = 0 To U.Groups.Count - 1
         Debug.Print "   " & U(i).Name
      Next
   End Function

STATUS

This behavior no longer occurs in Microsoft Access version 7.0.

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 14, "Securing Your Applications," pages 340-344

For more information about using DAO to Assign or View Permissions, please see the following article in the Microsoft Knowledge Base: