Access Cookbook - Ken Getz [217]
What query a form is based on; some users can see more records than others.
What data entry controls are visible; some users can enter more fields than others.
What toolbars are shown.
10.7. List All Users with Blank Passwords
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?
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:
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.
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
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
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.
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.