The LDBView Utility

If you have a number of Access databases on which you want to conduct surveillance, you may want to use a specific piece of software that's suited to the job. One tool that you should look at is LDBView, a program that Microsoft wrote for Access 97 that still works well on any database on any version of Access. This program comes as part of a download file from www.microsoft.com called JetUtils.exe. Download this file by using the link provided in the "Further Reading" section at the end of this chapter. On this download, you will find the following:

• The LDBView program, which helps you look at who is using your database.

• A comprehensive white paper on Jet database engine locking and .LDB (locking information) files.

• A program called DBLock that can help you understand record locking conflicts in your database.

From this package, the most important tool is the LDBView utility (shown in Figure 6-3). This program is easy to install and easy to use. Microsoft Knowledge Base Article No. 176670 says that the LDBView accomplishes the following tasks :

• Discover which users have been connected to the database and which users are currently connected to the database.

• Determine which user or users have left the database in a suspect state.

Figure 6-3: Finding a database from which you require a list of users.

To use the utility, open LDBView.exe and then select a database by choosing File ˜ Open, as shown in Figure 6-3.

The LDBView utility will work only if someone has the database open (in shared mode) and an .LDB file is generated. Once you have selected the database, you will see a form similar to that shown in Figure 6-4.

Figure 6-4: LDB Viewer, showing a list of users in a database.

The list on the right side of that form shows the following important information:

• User Name , which identifies the workstation as specified in the Control Panel under Network for NT/Windows 98 or System for Windows XP/2000.

• Logged On, which specifies the Access workgroup user name the user entered to log on to the database. If the user name and password prompt does not appear, this field returns Admin, the default Access user name.

• Suspect State, which returns True if the user has left the database in a suspect state. Otherwise, this value is Null.

• Committed Transactions, an integer value whose purpose this author was unable to determine. In addition, Access 97 databases were the only version where this column was populated .

Caution  

If you are using Access 2000, the suspect state will have a value of Yes once a user logs off. Also, the number of committed transactions will always remain 0. The database header information also always remains 0, but you really need to consider finding a new life if you find this interesting. When everyone closes down the database, the connection to the database ceases and the display is cleared.

The LDBView utility has the advantage that it looks directly at the .LDB file and skips Access security such as database passwords and workgroup security. Please note that the .LDB file holds no information that will compromise your database's security.

A Little More on the .LDB File

Whenever you open an Access database, Access will open a file with the same name as the database and an extension of .LDB. This file type is known as a locking information file. For example, if you open Northwind.mdb in the samples directory of your Access/Office installation, a file called Northwind.ldb will be generated. This new file keeps track of users and the locking status of every object that requires some form of locking in the database. When all users have exited the database by closing it in an orderly manner, Access will close the .LDB file and delete it.

The internals of the .LDB file are important for database sharing. The file stores the computer name and workgroup file security names of those users who are currently logged on to the database (with the occasional exception). It also stores the objects that that user has open or locked in a special and flexible part of the workgroup file.

Note  

Access needs to delete the .LDB file when it closes so that replicated databases work properly and to allow for performance improvements when determining which other users have locks. Therefore, everyone who uses the folder in which the database is stored will need write and delete permissions for files in that folder. You will find out more about Windows folder permissions in Chapter 12.

To find out more about the .LDB format, read the white paper Understanding Microsoft Jet Locking (LDB Files) by Kevin Collins. This paper is included in the LDBView download files, or you can find a link to it in the "Further Reading" section with other .LDB pages at the end of this chapter.

The Who Logged On Form

I've included a form in the demonstration database called frmWhoLoggedOn. (Australian Mark McNally wrote the form in 1996, as far as I can tell.) This form shows you how to read an .LDB file directly and displays the results in a list box. This form continues to show users after they have logged off until Access deletes the .LDB file. This file may be of interest if you want to find all users who have attached to a database rather than just those who were in the database when you looked at the user roster through ADO or the LDBView utility.