Recipe 10.7 List All Users with Blank Passwords

10.7.1 Problem

As database administrator, you need to ensure that every member of your workgroup has an Access password. You can use the NewPassword method to create a new password, and you understand why you can't retrieve the value of a user's password, but you need a way to find out whether a user has established a password yet. You'd like to create a list of all users, indicating which ones don't have passwords. How can you do this?

10.7.2 Solution

You can't retrieve users' passwords, but there's an easy way to find out if a user has a blank password: simply try to log onto the user's account using a blank password. If you succeed, you know the user has no password. With a lot of users this becomes a tiresome process, but fortunately, you can automate it using DAO and the CreateWorkspace method.

The frmUserPasswords form fills a table with a list of users and whether their passwords are blank and then presents this information to you in a list box. To test it, open and run frmUserPasswords from 10-07.MDB. Figure 10-21 shows the form in use for a sample workgroup.

Figure 10-21. frmUserPasswords shows users and password status for a sample workgroup

To use this information in your own applications, follow these steps:

Table 10-16. Table layouts for gathering user/group information

Field name	Field type	Primary key?
UserID	AutoNumber	Yes
UserName	Text	No
PasswordSet	Yes/No	No

Figure 10-22. The tblUsers table in design mode

10.7.3 Discussion

acbFindBlankPasswords uses DAO to do most of its work. It starts by setting up the object variables it needs to retrieve and store the password information. It uses the Workspace object to loop through all the users (since the Workspace object provides the Users collection that you'll use), and the Recordset object refers to the table into which you'll write the new data:

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.Databases(0)
Set rst = db.OpenRecordset("tblUsers")

You then need to clear out the previous contents of tblUsers, so that later code can fill in the table with the current list of users and their password status:

db.Execute "DELETE * FROM tblUsers"

The next step is to loop through the Users collection of the default Workspace object. For each user, the code attempts to create a new workspace, as shown here:

For intI = 0 To wrk.Users.Count - 1
   Set usr = wrk.Users(intI)
   '
   ' See the next code sample.
   '
Next intI

The final step is the important one. For each user, the code calls the CreateWorkspace method of the DBEngine object. To call this method, you must supply three parameters: the name for the new workspace (of course, since you only need the result of attempting to create the workspace, the actual name doesn't matter), the username, and the user's password. An empty string ("") is passed for the password. An error indicates that the current user has a password, since the new workspace could not be created using the blank password. If there was no error, then that user does not have a password.

The code checks whether an error occurred, comparing the Access built-in Err value with the known error value that occurs when you attempt to create a workspace with an invalid password. Regardless of whether an error occurred, the code adds a new row to tblUsers and stores the username along with the password status in the table. Here is the code for these steps:

' Skip the two special users, since you can't log in
' as either of them via CreateWorkspace( ).
If strUser <> "Creator" And strUser <> "Engine" Then
    ' Try to log in with a blank password. If this
    ' doesn't fail, the user has a blank password.
    Set wrkTest = DBEngine. _
     CreateWorkspace("Test", strUser, "")
    blnPwdUsed = (Err = acbcErrInvalidPassword)
    
    ' Add a new row to tblUsers, storing the user's
    ' name and whether or not they have a password.
    rst.AddNew
        rst("UserName") = strUser
        rst("PasswordSet") = blnPwdUsed
    rst.Update
    wrkTest.Close
End If

As discussed in the Solution in Recipe 10.5, the Users collection contains two users that are not actually part of your workgroup: Creator and Engine. Access creates these two users but doesn't allow you to log on as either one, either from the command line or by creating a new workspace. Therefore, the code just skips these special users, since we don't really care whether their passwords are blank.

If you intend to use acbFindBlankPasswords in a production environment, you may wish to add some error-handling code to the procedure. Any time you write to tables, you should include some method of dealing with errors. At the least, the user (which could well be yourself) should be alerted that an error has occurred and given some information about the error.