MS-Access / Getting Started

---

Creating and Deleting Groups

Rather than assign access permissions to individual users, as mentioned earlier, you can create groups to which one or more users can be assigned. Each group can be assigned specific permissions to the database's objects, and every user who is assigned to that group will inherit the permissions of that group. In this section, you learn how to use DAO to create or delete Groups in code. The following code shows how to create a new group. The basic procedure is to create the group using the CreateGroup method, and then append it to the Groups collection:

Public Sub CreateUserGroup(strGroupName As String,
strPID As String)
    Dim wrk As DAO.Workspace
    Dim grp As DAO.Group

    Set wrk = DBEngine(0)
    On Error GoTo CreateUserGroupErr

    'Create the new group
    Set grp = wrk.CreateGroup(strGroupName, strPID)
    ws.Groups.Append grp

CreateUserGroupErr:
    Set grp = Nothing
    Set wrk = Nothing

End Sub

Deleting a group is even easier. Simply execute the Groups collection's Delete method, as follows:

Public Sub DeleteGroup(strGroup As String)
    On Error Resume Next
    DBEngine(0).Groups.Delete strGroup
End Sub

You can't rename a group once it has been created. If you need to rename a group, you have to delete it, and then re-create it. Remember, though, if you need to re-create a user or group, you must supply the same PID that you used to create it in the first place.

Creating and Deleting Users

Using DAO, you can create a new user account that can then be added to one or more groups. The following code shows how to create a new user. The basic procedure is to create the user with the CreateUser method, and then append it to the Users collection:

Public Function CreateUserAccount(strUserName As String, _
    strPID As String, _
    strPassword As String)

    Dim wrk As DAO.Workspace
    Dim usr As DAO.User

    Set wrk = DBEngine(0)
    On Error GoTo CreateUserAccountErr

    'Create the new user
    Set usr = wrk.CreateUser(strUserName, strPID, strPassword)
    wrk.Users.Append usr

CreateUserAccountErr:
    Set usr = Nothing
    Set wrk = Nothing
End Function

As with deleting a group, deleting a user is quite simple; just execute the Users collection's Delete method:

Public Sub DeleteUser(strUser As String)
    On Error Resume Next
    DBEngine(0).Users.Delete strUser
End Sub

To rename a user account, you must delete the account, and then re-create it.

User and Group Operations

Before you can assign permissions that allow users to access any of the Access or database engine objects, you must add the users to one or more groups.

There are two ways you can do this: by adding users to the group, or by adding the group to the users. Although the following two procedures achieve exactly the same end, they demonstrate how to do it:

Example: Adding Users to Groups

Public Sub AddUser2Group(strUser As String, strGroup As String)
    Dim wrk As DAO.Workspace
    Dim usr As DAO.User
    Dim grp As DAO.Group

    Set wrk = DBEngine(0)
    On Error Resume Next

    'Create object references
    Set grp = wrk.Groups(strUser)
    Set usr = grp.CreateUser(strUser)

    'Add the group to the user's Groups collection
    grp.Users.Append usr
    grp.Users.Refresh

    Set usr = Nothing
    Set grp = Nothing
    Set wrk = Nothing
End Sub

Example: Adding Groups to Users

Public Sub AddGroup2User(strUser As String, strGroup As String)
    Dim wrk As DAO.Workspace
    Dim usr As DAO.User
    Dim grp As DAO.Group

    Set wrk = DBEngine(0)
    On Error Resume Next

    'Create object references
    Set usr = wrk.Users(strUser)
    Set grp = usr.CreateGroup(strGroup)

    'Add the group to the user's Groups collection
    usr.Groups.Append grp
    usr.Groups.Refresh

    Set usr = Nothing
    Set grp = Nothing
    Set wrk = Nothing
End Sub

Similarly, if you want to delete a user from a group, you can delete the user's entry from the Groups collection, or delete the group from the Users collection. Here's one way:

Public Sub DeleteUserFromGroup(strUser As String, strGroup As String)
    Dim wrk As DAO.Workspace

    Set wrk = DBEngine(0)
    On Error Resume Next

    wrk.Users(strUser).Groups.Delete strGroup

    Set wrk = Nothing
End Sub

Determining If a User Belongs to a Specific Group

When determining if a user should have access to a particular object or function, you may need to determine whether the user belongs to a specific group. As with most other functions, this is also fairly easy; simply check if the name of the user exists in the group's Users collection, or if the group exists in the user's Groups collection.

Public Function IsUserInGroup (strUser As String, strGroup As String) As Boolean
    Dim wrk As DAO.Workspace
    Set wrk = DBEngine(0)

    On Error Resume Next

    IsUserInGroup = False

    'Check in the Users --> Groups collection
    IsUserInGroup = _
	(wrk.Users(strUser).Groups(strGroup).Name = strGroup)

    'You can also do it this way...
    'Check in the Groups --> Users collection
    'IsUserInGroup = _
	(wrk.Groups(strGroup).Users(strUser).Name = strUser)

    Set wrk = Nothing
End Function