Access Cookbook - Ken Getz [218]
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.
10.8. Track Which Users Have a Shared Database Open
Problem
You need better control over a networked Access application. Is there any way you can track which users are logged in and which machines they are using?
Solution
Access tracks this information in the .LDB file, but that file sometimes lists users who have already logged out, so you can't just open it in Notepad and take a look. This solution opens a special ADO recordset that shows you exactly the information you need. The sample form lists user and machine names in a list box.
Import frmCurrentConnections (see Figure 10-23), which shows which users are logged into any shared database. Note that if you are using a split architecture, the shared database is the one that contains your tables. Open the VBA Editor and use the Tools → References dialog to ensure that you have a reference to Microsoft ActiveX Data Objects, Version 2.1 or later.
Figure 10-23. frmCurrentConnections shows which users are logged