User Object, Users Collection Example (MDB)

The following example creates a new User object and appends it to the Users collection of a Workspace object. It then creates a new Group object and appends it to the Groups collection of the Workspace object. The new User object is also appended to the Users collection of the Group object. The new user is then given read-data permissions on tables.

Note that in order to assign users to groups, you must either append a User object to the Users collection of a Group object, or append a Group object to the Groups collection of a User object. It doesn't matter which option you choose; either will result in the specified user being included in the specified group.

Note   When programming security, you should avoid including password and PID information in your code. The following example is intended for demonstration purposes only.

Sub NewTablesUser()
    Dim wsp As Workspace, dbs As Database
    Dim usr As User, grp As Group, usrMember As User
    Dim ctr As Container, doc As Document

    ' Return reference to default workspace.
    Set wsp = DBEngine.Workspaces(0)
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create User object and append to Users collection
    ' of Workspace object.
    Set usr = wsp.CreateUser("Chris Jones", _
        "123abc456DEF", "Password")
    wsp.Users.Append usr
    ' Create Group object and append to Groups 
    ' collection of Workspace object.
    Set grp = wsp.CreateGroup("Marketing", "321xyz654EFD")
    wsp.Groups.Append grp
    ' Append new User object to Users collection of
    ' new Group object.
    Set usrMember = grp.CreateUser("Chris Jones")
    grp.Users.Append usrMember
    ' Refresh Users collection of Group object.
    grp.Users.Refresh
    ' Return Container object.
    Set ctr = dbs.Containers!Tables
    ' Set UserName property of Container object.
    ctr.UserName = usrMember.Name
    ' Add retrieve permissions for new user on all tables.
    ctr.Permissions = ctr.Permissions Or dbSecRetrieveData
    Set dbs = Nothing
    Set wsp = Nothing
End Sub