Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Create a table to hold the information. Either import the table
   tblUsers from

   10-07.MDB , or use the information
   in Table 10-16 to create your own table. Figure 10-22 shows the table in design mode.

Figure 10-22. The tblUsers table in design mode

2. Either import the module basFindBlank from
   

   10-07.MDB , or enter the following code into a
   global module. This is the code you'll use to fill
   the table you just created.

   Public Sub acbFindBlankPasswords( )
   ' Fill tblUsers with list of users, and
   ' whether or not their password is blank.
   Dim intI As Integer
   Dim usr As DAO.User
   Dim db As DAO.Database
   Dim wrk As DAO.Workspace
   Dim wrkTest As DAO.Workspace
   Dim rst As DAO.Recordset
   Dim blnPwdUsed As Boolean
   Dim strUser As String
   Const acbcErrInvalidPassword = 3029
   ' Set up object variables.
   Set wrk = DBEngine.Workspaces(0)
   Set db = wrk.Databases(0)
   Set rst = db.OpenRecordset("tblUsers")
   db.Execute "DELETE * FROM tblUsers"
   On Error Resume Next
   ' Loop through all the users.
   For intI = 0 To wrk.Users.Count - 1
   Set usr = wrk.Users(intI)
   strUser = usr.Name
   ' 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
   Next intI
   rst.Close
   End Sub

3. To produce a list of all users whose passwords are blank, execute the
   code in acbFindBlankPasswords. You can call it
   from the debug window, or from an event procedure, as in
   frmUserPasswords. (If you decide to use
   frmUserPasswords, you must also create a query,
   qryUserPasswords, which sorts the rows in
   tblUsers in ascending order on the UserName field.
   This query fills the list box on the sample form.) You could create a
   report that pulls its rows from tblUsers as well,
   allowing you to prepare a regular report listing all users with blank
   passwords.

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

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.