HandsOn Creating a Group Account

This hands-on requires that you have completed Custom Project 17-1.

1. Start Microsoft Access without opening a database.

2. Choose Tools | Security | Workgroup Administrator. Access displays the Workgroup Administrator dialog box where you can check the path to the workgroup information file that is currently used. Perform one of the following steps:

a. If System.mdw appears in the Workgroup path, click OK to exit the Workgroup Administrator dialog box and proceed with step 3 below.

b. If the Workgroup path includes the Security.mdw file that was created in Custom Project 17-1, click the Join button to join another workgroup. Use the Browse button in the Workgroup Information File dialog box to select and open System.mdw. Refer to the beginning of this chapter for information on the default location of this file. Once you select the correct file, the dialog box should display its full path. Click OK to exit this dialog box. Access will display a message box saying you successfully joined the workgroup defined by the selected information file. Click OK to the message and click OK in the Workgroup Administrator dialog box to exit.

Part II

3. Open the Acc2003_Chap17.mdb file from the downloadable files, or create this file from scratch using the Microsoft Office Access user interface.

4. Switch to the Visual Basic Editor window and insert a new module.

5. Because you will be programming using the ADOX and JRO Object Models, you need to set up the reference to these libraries. Choose Tools | References and click the check box next to Microsoft ADO Ext. 2.7 for DDL and Security and Microsoft Jet and Replication Objects Library. After making these selections, click OK to exit the References dialog box.

6. In the module's Code window, enter the Create_Group procedure as shown below.

Sub Create_Group()

Dim cat As ADOX.Catalog Dim conn As ADODB.Connection Dim strDB As String Dim strSysDb As String Dim strName As String

On Error GoTo ErrorHandle strDB = "C:\BookProject\SpecialDb.mdb" strSysDb = "C:\BookProject\Security.mdw" strName = "Masters"

' Open connection to the database using the specified system database Set conn = New ADODB.Connection With conn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Jet OLEDB:System Database") = strSysDb .Properties("User ID") = "Developer" .Properties("Password") = "chapter17" .Open strDB End With

' Open the catalog and create a group account Set cat = New ADOX.Catalog With cat

.ActiveConnection = conn .Groups.Append strName End With

MsgBox "Successfully created " & strName & " group account."


Set cat = Nothing conn.Close Set conn = Nothing Exit Sub ErrorHandle:

If Err.Number = -2147467259 Then

MsgBox strName & " group already exists."

Creating and Manipulating Databases with ADO


MsgBox Err.Description End If

Resume ExitHere End Sub

Upon executing the above procedure, a new group account named Masters will be established in the database that you created in Custom Project 17-1. Notice that before opening the database we need to set the Jet OLEDB:System Database property in the Properties collection of the ADO Connection object to specify the path and name of the workgroup information file that should be active when the database is opened. We also set the User ID and Password properties to log onto the database. After opening the database, we open the Catalog object and use the Append method of the Catalog's Groups collection to add a new group account. The Groups collection contains all groups in the specified workgroup information file. You can verify that the Masters group account was indeed created by opening the SpecialDb.mdb file. If you'd like to take a moment now, open this database using the shortcut on your desktop. Once the database is open choose Tools | Security | User and Group Accounts (Figure 17-11). Notice that the database contains the Masters group in addition to the default Admins and Users groups. Close the SpecialDb database and the Access application window in which it was displayed. Be careful not to close the Acc2003_Chap17 database you are working with.

To create a new group account in the current database, your VBA procedure will look like this:

Sub Create_Group2()

Dim cat As ADOX.Catalog

On Error GoTo ErrorHandle

Figure 17-11: Running the procedure in Hands-On 17-1 adds a new group account named Masters.

' Open the catalog and create a new group account Set cat = New ADOX.Catalog With cat

.ActiveConnection = CurrentProject.Connection .Groups.Append "Masters" End With


Set cat = Nothing Exit Sub ErrorHandle:

Part II

If Err.Number = -2147467259 Then

MsgBox "This group already exists."


MsgBox Err.Description End If

Resume ExitHere End Sub

0 0

Post a comment