Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Using Code to Maintain Groups

Chapter 27, "Database Security Made Easy," discusses the importance of creating logical groups of users and then assigning rights to those groups. The administrator of your application might want to add or remove groups after you have distributed your application. You can use ADO code to create and manage group accounts at runtime.

Adding a Group

You add a group using the Append method of the Groups collection. The Groups collection is part of the ADO Extensions for DDL and Security (ADOX) Catalog object. Figure 28.1 shows a form that enables users to add and remove groups.

Figure 28.1. This form enables administrative users to add and remove groups.

This form is named frmMaintainGroups and is included in the CHAP28EX.MDB database located on the sample code CD-ROM. Listing 28.1 shows the code under the Add button.

Listing 28.1 Adding a Group

Private Sub cmdAdd_Click() Dim boolSuccess As Boolean If IsNull(Me.txtGroupName) Then MsgBox "You Must Fill In Group Name Before Proceeding" Else boolSuccess = CreateGroups() If boolSuccess Then MsgBox "Group Created Successfully" Else MsgBox "Group Not Created" End If End If End Sub

This code tests to ensure that entries have been made for the group name. If so, the code calls the CreateGroups function. Based on the return value from CreateGroups, the code notifies the user whether it was able to successfully create the group. Listing 28.2 uses the Append method of the Groups collection to add a new group to the workgroup.

Listing 28.2 Using the CreateGroups Function to Add a Group

Function CreateGroups() As Boolean On Error GoTo CreateGroups_Err Dim cat As ADOX.Catalog CreateGroups = True Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection 'Append group to the Groups collection 'of the Catalog object cat.Groups.Append (Me.txtGroupName) CreateGroups_Exit: Set cat = Nothing Exit Function CreateGroups_Err: MsgBox "Error # " & Err.Number & ": " & Err.Description CreateGroups = False Resume CreateGroups_Exit End Function

The function uses a Catalog variable. The Catalog variable is part of the Microsoft ADOX. You must reference the ADOX library before you can use the Catalog variable. The example sets the ActiveConnection property of the Catalog variable to the connection associated with the current project. After the code establishes the connection, it uses the Append method of the Groups collection of the Catalog object to append the group. The Append method of the Groups collection receives one parameter, the name of the group. The Append method, when applied to the Groups collection, adds a new group to the catalog. The function uses the value in txtGroupName as the name of the group to add. After running this routine, you can verify that a new group has been added to the workgroup by choosing Tools, Security, User and Group Accounts. The newly created group should appear in the group drop-down list on the group page.

Removing a Group

The code to remove a group is very similar to the code required to add a group. Listing 28.3 shows the code under the cmdRemove command button.

Listing 28.3 Removing a Group

Private Sub cmdRemove_Click() Dim boolSuccess As Boolean If IsNull(Me.txtGroupName) Then MsgBox "You Must Fill In Group Name Before Proceeding" Else boolSuccess = RemoveGroups() If boolSuccess Then MsgBox "Group Removed Successfully" Else MsgBox "Group Not Removed" End If End If End Sub

This routine ensures that the user has filled in the group name and then calls the RemoveGroups function. The code displays an appropriate message, indicating whether it successfully removed the group. Listing 28.4 shows the RemoveGroups function.

Listing 28.4 Using the RemoveGroups Function to Remove a Group

Function RemoveGroups() On Error GoTo RemoveGroups_Err Dim cat As ADOX.Catalog RemoveGroups = True Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection 'Delete group from the Groups collection 'of the Catalog object cat.Groups.Delete Me.txtGroupName.Value RemoveGroups_Exit: Set cat = Nothing Exit Function RemoveGroups_Err: If Err.Number = 3265 Then MsgBox "Group Not Found" Else MsgBox "Error # " & Err.Number & ": " & Err.Description End If RemoveGroups = False Resume RemoveGroups_Exit End Function

The RemoveGroups function uses the Delete method of the Groups collection of the Catalog object, taking the value in txtGroupName as the name of the group to remove. If the group does not exist, an error number 3265 results. An appropriate error message appears.