Chapter 6: User and Object Surveillance

Overview

In this chapter, you will learn how to find out who and how many people are using your database. This important surveillance information can help you with administration issues such as asking users to log off the database or network for maintenance and upgrades and with targeting security for your database. To help you with these issues and other related topics, this chapter has a number of easy-to-install Access objects and discusses some free products that you can use. You should consider deploying some of these solutions in your database because they may help you balance speculation with facts, something that is always a good idea before embarking on software upgrades, such as adding security.

To get you up to speed quickly with the tools necessary for surveillance, I start by providing you with an Access form that will allow you to view computer and Access workgroup logon names . Though this tool is useful for individual databases, if you have a large number of databases, you may want to use a tool such as the LDBView or the Access Workbench to find who is using these databases without opening the database itself.

There are a number of occasions where it is useful to keep a detailed log of who is using the database. To assist you with that, you can include software from the download database to capture the Windows user account and time that someone opened and closed the database. What I find useful about this information is that it allows you to monitor usage over time and diagnose who was using a database when a problem occurred. I will also show you how to detect whether someone has connected to your database without using the startup form or the AutoExec macro.

To be more specific, the demonstration material included for this chapter includes a number of forms and Visual Basic for Applications (VBA) examples like the following:

• A form that lists computer names and allows you to stop new users from opening the database.

• A free license for Access Workbench 1.3 that I wrote. This license allows you to list all users of your database, even if it is protected by workgroup security or a database password.

• Access objects that log Windows user IDs, computer names, workgroup IDs, and the time that the user opens and closes the database.

• Access forms and modules that log the use of forms or reports in the database.

• A form that identifies database users who deliberately or unintentionally avoid your startup form or AutoExec macro.

Note  

To find the demonstration material, open the download database for your version of Access ”for example, grMAP97.mdb ”and choose Chapter 6.