Understanding Microsoft Jet Locking

Kevin Collins
Microsoft Jet Program Management

November 21, 1995

Click to open or copy the utilities discussed in this technical article.

Abstract

The purpose of this paper is to introduce you to Microsoftฎ Jet version 2.x and Microsoft Jet version 3.0 locking techniques so that you can apply the information to coding and debugging when developing multiuser applications. This paper addresses the following concepts:

Layout of the .LDB File

The .LDB file plays an important role in the Microsoftฎ Jet multiuser scheme. This file stores the computer and security names and has extended byte range locks placed on it by Microsoft Jet.

Microsoft Jet retrieves the computer name by making a request to the operating system. To modify the computer name in Microsoft Windowsฎ for Workgroups, Microsoft Windows 95, or Microsoft Windows NTฎ, simply go to the Control Panel and choose the Network icon. From there, you can change the computer name. Windows for Workgroups stores the computer name in the SYSTEM.INI file, whereas Windows 95 and Windows NT store the computer name in the Windows Registry. The security name is determined by passing a value to the Workspace object in Data Access Object (DAO). The default security name is Admin.

Extended byte range locks are locks placed outside the physical boundaries of a file—no data is ever physically locked. For example, you can place a lock at 10 million hex for a file that has a physical size of only 64 bytes. In other words, a lock is virtually placed at a location that does not exist on the hard disk. This type of locking is used because extended byte range locks are not limited by the size of the physical file, allowing for locking algorithms that would not otherwise be possible. Also, placing locks inside a data file would prevent other users from reading that data. In the early Borlandฎ dBASEฎ days, a user could place a lock on a row located in the data file that prevented anyone from reading that data: when printing a report, for example.

One .LDB file is always created for each Microsoft Jet database file that is connected in a shared mode. The .LDB file always retains the same name as the database that was opened (for example, NWIND.MDB always has a NWIND.LDB), and the .LDB file is always in the same directory as the database. If an .LDB file does not exist and the database is connected in a shared mode, an .LDB file is created. The physical structure of the .LDB file is best thought of as a one-dimensional array. Each element in the array, up to 255, consists of 64 bytes. The first 32 bytes contain the computer name (such as KevinCol), and the second 32 bytes contain the security name (such as Admin). This data is then used to provide information regarding which other users are holding locks. You can view this information by simply looking at the contents of the .LDB file.

The physical size of the .LDB file never exceeds 16,320 bytes, because the maximum number of concurrent users in a Microsoft Jet database is 255 (255 * 64 = 16,320).

The high-order ASCII characters seen in Microsoft Jet 2.x .LDB files are meaningless to both the viewer and to Microsoft Jet. Microsoft Jet 3.0 eliminates the high-order ASCII characters from the .LDB file so that only the computer and security names are shown.

Microsoft Jet 3.0 automatically deletes the .LDB file when the last user closes the database. This is done to aid in issues with replicated databases and to allow for performance improvements when determining which other users have locks. The exception to this is when a user does not have networked operating system or operating system (NOS/OS) delete rights to the .LDB file or if the database is in a suspect state. There is no performance or concurrency benefit gained from deleting your .LDB file in a Microsoft Jet 2.x environment.

A 32-bit utility (LDBView), which is included with this article, enables you to view the status of the commit byte(s) (commit bytes are described in the next section) and what users are currently logged onto the database. This utility helps you determine which user(s) left the database in a suspect state.

Another utility (MSLDBUSR.DLL), which is included with this article, now enables you to programmatically retrieve the following information if you're running 32-bit Microsoft Visual Basicฎ for Applications (VBA) applications:

You can find out how to use this dynamic-link library (DLL) at the end of this paper.

Database Header Page

The database header page (DBH) is the first page in a database. It is partially used to store the commit byte(s) for each of the 256 possible users connected to a database. A commit byte (or pair of bytes in version 3.0) is a value that is used by Microsoft Jet to determine the state of the database.

Microsoft Jet 2.x utilizes 256 bytes that store the commit bytes for each possible user starting at 700 hex and continuing to the end of the page (800 hex). Microsoft Jet 3.0 utilizes 512 bytes that use two bytes per user starting at 600 hex. The first byte (Microsoft Jet 2.x) or the first two bytes (Microsoft Jet 3.0) are used only when a database is connected in an exclusive mode and the remaining 255 bytes (Microsoft Jet 2.x) or 510 bytes (Microsoft Jet 3.0) are used when the database is connected in a shared mode.

Commit bytes in Microsoft Jet 2.0 have only two valid values, FF and 00. A value of 00 represents a neutral state and a value of FF means that Microsoft Jet is in the process of physically writing data to disk. If there is a value of FF and no corresponding user lock, then a user has had an abnormal shutdown of the database file. New users attempting to connect to the database receive the message "Database is corrupted or is not a database file," and they will be forced to run repair on the database to open it. An anomaly in Microsoft Jet 2.0 allowed instances where the database could be closed with a value of FF, thus preventing other users from connecting to the database until repair could be run. This was corrected in Microsoft Jet version 2.5.

Commit bytes in Microsoft Jet 2.5 have five valid values ranging from 00 to 04. This range of values provides more information on what users were doing when the database is left in a suspect state. Similar to Microsoft Jet 2.0, the database is in a suspect state if there is a nonzero value without a corresponding user lock. A value of 00 represents a neutral state. A value of 01 indicates that a user accessed a corrupted page in the database. A value of 02 indicates that the database is being created. A value of 03 indicates that the database is being repaired, and a value of 04 indicates that the user is in the process of physically writing data to disk.

Commit bytes in Microsoft Jet 3.0 can have many different values, so they were increased to 2 bytes. A value of 00 00 indicates that the user is in the process of physically writing to disk, and a value of 01 00 indicates that a user has accessed a corrupted page. Therefore, if either a value of 00 00 is present without a corresponding user lock or a value of 01 00 is present, users are not allowed to connect to the database without first executing the repair utility. If a user is looking at these commit byte values with the LDBView utility, he or she will notice that there may be many other values present in the 600 to 800 hex range. These values are used internally by Microsoft Jet for performance reasons (mainly to determine whether other users have written data to the database file). If Microsoft Jet determines that other users have not written information to disk, it delays refreshing its internal cache, resulting in fewer reads and increased performance. Therefore, it is possible that a database opened in shared mode may approach the performance levels of a database opened in exclusive mode.

Again, which commit bytes are actually used for a user is determined by their corresponding user lock in the .LDB file. A detailed discussion of how these locations are obtained appears later in this paper. Also, a user can monitor these values for both Microsoft Jet 2.x and 3.0 databases by using the 32-bit LDBView utility included with this article.

Types of Microsoft Jet Locks

Shared vs. Exclusive Locks

These two types of locks are referred to frequently later in the paper, and it is important to understand the concepts behind them. Regardless of the type of lock, each extended byte range lock placed on the .LDB file always represents one page in the .MDB file (except for user locks). No locks are ever placed on the .MDB file.

A shared lock occupies only 1 byte and never conflicts with another shared lock. These types of locks are typically used to allow many people to read information at the same time. Shared locks are used, for example, when many users have the same table open at the same time without conflicting with each other or when users are reading an index that participates in referential integrity. In these situations, many users would be able to read the index at the same time.

An exclusive lock spans between 256 and 512 bytes and always conflicts with other shared locks and other exclusive locks. The exclusive lock always locks the first 256 bytes of the range to prevent any shared locks from being set and determines whether there are any existing shared locks set. In addition, it locks enough additional bytes beyond 256 to determine which user is holding the lock. An example of an exclusive lock is when a user opens a table in deny-read mode. This exclusive lock would be prevented if another user had a shared lock on the table; if the exclusive lock were acquired, it would prevent other users from obtaining a shared lock. Another example is when a user is writing information back to an index page that participates in referential integrity. This would produce a write lock, which is always exclusive, and would prevent anyone from reading the index page. The index page requires a read lock and is always shared.

There are seven types of Microsoft Jet locks:

These extended byte range locks are broken out into six virtual regions off of the .LDB file for Microsoft Jet 2.x and five virtual regions for Microsoft Jet 3.0. These areas are where Microsoft Jet places extended byte range locks that range from 10000001 to 6FF800FF hex. These locks are present only when users have the database connected in a shared or read-only mode. Note that the names of these locks were assigned by the Microsoft Jet development team and do not necessarily have the same meaning that other database vendors use. See Table 1 after the descriptions below for a summary of these locking ranges.

User Locks

User locks determine ownership of a commit byte(s) in the DBH, write the computer and security names in the correct location in the .LDB file, and retrieve the computer and security names of another user who has conflicting locks. A user lock is obtained and is persistent for as long as a user is connected to the database. (Persistence in this context indicates the duration of a lock or how long a lock is held.) Only one user lock exists for each connected user; however, multiple instances of Microsoft Jet on the same computer create an individual user lock for each instance. User locks are always in the range of 10000001 hex through 100000FF hex. User locks occupy only 1 byte (for example, 10000002 to 10000002).

Write Locks

Write locks prevent other users from changing data while a user is modifying data. A write lock is typically placed on data, index, or long value pages. (Long value pages are a type of data page that contains ANSI SQL data types of CHARACTER, VARYING, BIT, or BIT VARYING. These data types are known as Memo or OLE Object fields in Microsoft Access and Visual Basic.) Write locks are in the range of 20000000 hex through 2FF800FF hex and always span between 256 and 512 bytes (for example, 2000E600 to 2000E700 hex), and therefore are always exclusive locks. The persistence of write locks is directly related to the duration of a transaction. All SQL Data Manipulation Language (DML) statements have implicit transactions placed around them. Thus, an UPDATE statement has persistent write locks until the entire update is committed. The persistence of write locks is also determined by the type of locking that is chosen for recordset navigation and form editing (for example, pessimistic/optimistic) and whether explicit transactions are used. For more information on locking semantics, see Chapter 12 in the Microsoft Access 2.0 Building Applications manual.

Read Locks (Microsoft Jet 2.x)

Read locks, which are a type of shared lock, are primarily used for the immediate recycling of index pages and for ensuring that index pages in the Microsoft Jet cache are up-to-date. This type of lock is placed on long value pages and index pages. It is used to prevent an index page from being recycled while that page is being referenced in the Microsoft Jet cache and to provide an integral view of the index. Read locks are placed in the range of 30000000 to 3FF800FF hex. They occupy only 1 byte (for example, 30001A01 to 30001A01).

Read locks in version 2.x are probably the most troublesome types of locks to deal with because they can cause locking conflicts that are not obvious to the developer or user. The persistence of read locks is determined by:

  1. The LockedPageTimeout setting, specified in tenths of a second, which determines the amount of time the Microsoft Jet database engine retains locks before releasing them. By increasing the LockedPageTimeout setting, read locks are retained for a longer period of time, which reduces the need for Microsoft Jet to reread data pages into cache. The LockedPageTimeout setting can be changed by modifying the \ISAM section of the corresponding .INI file (MSACC20.INI for Microsoft Access users) or by modifying the value in the Windows Registry for Windows 95 or Windows NT in the Microsoft Jet 2.x\ISAM key. The side-effects could be reduced concurrency due to commit lock conflicts, which are discussed next.

  2. Calling the DBEngine.Idle DBFreeLocks statement or the FreeLocks statement. However, these commands do not always free all read locks when inside a transaction (see lock log examples later in this article). Note that the DBFreeLocks constant is used in DAO 3.0 and would be used when Microsoft Access version 7.0 or Visual Basic version 4.0 has opened a Microsoft Jet 2.x database. If Microsoft Access 2.0 or Visual Basic 3.0 is used, then the constant would be DB_FreeLocks.

Read locks are typically placed when an index or long value page is read and placed in the Microsoft Jet cache, for example, when performing an SQL DML UPDATE command on a table that has a primary key.

Read Locks (Microsoft Jet 3.0)

The main purpose of read locks in Microsoft Jet 3.0 is to allow multiple users to read long value data, but prevent users from writing to it while others are reading.

Microsoft Jet 3.0 has reduced the amount of read locks that are placed on index pages, which directly results in greater concurrency and performance. Read locks on index pages are now placed only when referential integrity is being enforced. Unlike Microsoft Jet 2.x, the persistence of read locks is determined by the currency of a row. Thus, a read lock on a long value page remains until the user leaves that row. An exception to this is when a long value page contains data from more than one row, at which point Microsoft Jet releases the read lock on that page. An example of this is when several rows of Memo data are placed on one long value page.

Because read locks are persistent until the user loses currency on the row, the DBEngine.Idle DBFreeLocks statement and FreeLocks statement no longer provide any functionality. (the FreeLocks statement is no longer supported in Visual Basic 4.0 or Microsoft Access 7.0, unless the DAO 2.5/3.0 compatibility layer is enabled.) Microsoft Jet 3.0 read locks are also placed in the same range as write locks; the only difference is that read locks are shared locks and they occupy only 1 byte (for example, 20001A01 to 20001A01).

Commit Locks

Commit locks are present only in Microsoft Jet 2.x and are very similar to read locks, except that they are always exclusive locks. These locks are also placed in the 30001A01 through 3FF800FF hex range. They are placed when either index pages or long value pages are being written back to the database, and they conflict only with read locks. Read locks are typically placed on index pages whenever an index page is placed in the Microsoft Jet cache. An index page references many data pages, and therefore it becomes apparent why many users can experience locking conflicts when they know other users are not editing data on the same data page.

One of the best mechanisms to prevent these conflicts is to judiciously use the DBEngine.Idle DB_FreeLocks statement or FreeLocks statement in the application code. The use of these two commands is the most effective way to free up read locks so that commit locks do not conflict with them.

Commit locks do not exist in Microsoft Jet 3.0 because the range for read locks has been moved into the same range as write locks, thus eliminating the need for this type of lock.

Table-Read Locks

Table-read locks are used to control placing a table in a deny-read mode, which uses an exclusive lock and prevents other users from reading data from the table. Unlike the previous types of locks, table-read locks and the rest of the table-type locks are placed only on a special type of page called a table header page (TBH). There is one TBH page for each table, and every TBH contains statistics about the table (such as row count, next counter value, column data types, and index types).

Table-read locks are placed in the 40000000 through 4FF800FF hex range and can be placed as shared locks or exclusive locks.

When a table is opened, a shared table-read lock is placed. Deny-read mode is set when the default locking on a database is set to All Records (through the Microsoft Access user interface) or dbDenyRead is issued through the DAO OpenRecordSet method. If the exclusive lock can be obtained, then there are no other users that have the table opened. The exclusive lock prevents other users from acquiring shared locks when they try to open a table. These locks are persistent until the user closes the table.

Table-Write Locks

Table-write locks are used in conjunction with table deny-write locks and are placed in the 50000000 through 5FF800FF hex range. These shared locks are persistent whenever a table is opened in a state that allows writing.

Table Deny-Write Locks

These locks are used in conjunction with table-write locks and are explicitly set when opening a table in deny-write mode. These locks are placed in the 60000000 through 6FF800FF hex range and have a persistent shared lock while the table is opened in deny-write mode. An exclusive lock is placed, but not held, to determine which other users have the table open in deny-write mode.

Table 1



Lock name


What sets the lock


What is locked

What the lock prevents

Persistence of the lock
Shared or exclusive available
Byte range examples
User User opens an .mdb file N/A Nothing Until user exits .mdb file N/A 10000001–
10000001
Write Insert, update, or delete All available page types Updates or deletes to data and sometimes inserts to a table Controlled by default locking (optimistic or pessimistic) and the duration of a transaction Exclusive 2000A601–
2000A701
Read (version 2.x) Reads on a long value or an index page Long value, index, or TBH Updates or deletes to long value pages or index Controlled by LockedPageTimeout in the .INI file or by issuing a FreeLocks call Shared 30001E01–
30001E01
Read (version 3.0) Reads on certain long values or index pages when referential integrity is being enforced Long value, index, or directory pages Updates or deletes to long value or index pages Until a read or a transaction is complete or the user moves to a new row Shared 20063801–
20063801
Commit (dropped in version 3.0) Writes to long value or index pages Long value or index pages Prevents reads when data is being written to disk Until data is finished being written to disk Exclusive 30001A01–
30001B01
Table-read Shared lock is obtained when a table is opened; exclusive lock is obtained when default locking is set to All Records through Microsoft Access or Set dbDenyRead has been issued through DAO OpenRecordSet. TBH Prevents exclusive read lock from being set As long as the table is open Both 4000C801–
4000C801
Table-write Shared lock is obtained when a table is opened; exclusive lock is obtained when default locking is set to All Records through Microsoft Access or dbDenyRead has been issued through DAO OpenRecordset. TBH Prevents exclusive write lock from being set, does not prevent table deny-write lock As long as the table is open Both 5000C801–
5000C801
Table deny-write Can be set only through DAO TBH Prevents all writes to the table As long as the table is open Both 6000C801–
6000C801

Deciphering Lock Ranges

To use the information previously discussed in a way that can help you resolve locking issues with your application, you need to be able to view, understand, and decipher the locks that Microsoft Jet places.

To view the locks being placed, you need to have either a Novellฎ NetWareฎ environment, Microsoft SMS Network Monitor, or some other "network sniffing" tool. By using one of these utilities, particularly the real-time NetWare Monitor program, an administrator can view the locks being placed by Microsoft Jet on the .LDB file. Developers can also use this information to see how their code places locks on the .LDB file and what effects it might have in a multiuser environment.

User Lock Algorithm

All Microsoft Jet multiuser locking schemes revolve around the placement of a user lock. As stated previously, user locks are placed in the 10 million hex range and occupy only 1 byte. When Microsoft Jet connects to a database in a shared or read-only mode, the following activities occur before a user lock is actually placed.

The first activity is a determination whether the Microsoft Jet database engine is in a suspect state. This is done by examining the DBH page and seeing what bytes have a nonzero value. If the first byte (Microsoft Jet 2.x) or the first 2 bytes (Microsoft Jet 3.0) contain a commit in progress value, or the remaining 255 bytes (Microsoft Jet 2.x) or 510 bytes (Microsoft Jet 3.0) have a commit in progress value and do not have a corresponding user lock, then Microsoft Jet forces the user to repair the database. When opening a database in shared mode, there is never a need to check for a user lock on the first byte (Microsoft Jet 2.x) or the first two bytes (Microsoft Jet 3.0), because the lock is only used when a database is connected exclusively. A corresponding user lock would be a lock that shares the same offset in the ten-million-hex range as the offset from the first byte in the DBH page. Thus, a nonzero commit byte at 701 hex (Microsoft Jet 2.x) or 602 to 603 hex (Microsoft Jet 3.0) would need to have a user lock at 10000001 in order for the database to be opened without a corrupt database message.

The Microsoft Jet database engine then opens the .LDB file (or creates one if one does not exist) and tries to place a lock at 10000001 hex. If Microsoft Jet is successful in obtaining this lock, it writes the computer and security name to the first 64 bytes of the file. If Microsoft Jet cannot acquire this lock, it continues moving one byte further until a lock can be successfully obtained. After the user lock is acquired, the Microsoft Jet database engine writes the computer and security name at the corresponding location in the .LDB file. For example, a user lock at 10000040 hex would write an entry starting at 4096 bytes in the physical part of the .LDB file.

Some users have experienced delays when trying to connect to a database that already has many users connected. This is mainly due to nonoptimized network drivers, for example, when a user is running a Microsoft Windows NT 3.5 client accessing a NetWare server. If a user were trying to connect to the database and 30 other users were currently connected, it could take upwards of 30 seconds to perform the user lock algorithm. Windows NT 3.51 has a modified NetWare requester driver that decreases the wait to approximately two seconds.

Deciphering Write Locks

As stated previously, write locks always occur in the 20 million hex range and always have a starting and ending lock range between 256 and 512 bytes. The code below places write locks on the .LDB file that relates to pages in the Customers table. All future references to locks on pages are really referring to locks placed on the .LDB file that relates to pages in the database file.

Note   The following code examples assume that the user is using Visual Basic 4.0 or Microsoft Access 7.0 with either an original Microsoft Jet 2.x version of the NWIND database that shipped with Access 2.0, or a NWIND database that was compacted to Microsoft Jet 3.x from the original Access 2.0 NWIND database (using the DAO CompactDatabase method in Visual Basic 4.0 [32-bit] or Microsoft Access 7.0). If Microsoft Access 7.0 is used to convert the Microsoft Access 2.0 NWIND database using the Convert option from the Tools/Database Utilities menu, then the logs differ from the Microsoft Jet 3.0 logs below. The logs also assume that the user is the first user logged on to the database, thus implying a user lock of 10000001 hex.

Data Page Write Locks

Here is sample code and a lock log (Table 2) of write locks placed on data pages:

Sub WriteLocksDataPages ()
    'The example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Dim ws As Workspace
    Dim ContactName As String
    Set db = OpenDatabase("NWind.mdb", False, False)
    Set rs = db.OpenRecordset("SELECT * FROM Customers")
    rs.lockedits = false
    Set ws = Workspaces(0)
    ws.BeginTrans
    While Not rs.eof
        rs.Edit
        ContactName = rs![Contact Name]
        rs![Contact Name] = ContactName
        rs.Update
        rs.MoveNext
    Wend
    ws.CommitTrans
End Sub

When you set a breakpoint on the recordset MoveNext method, the Microsoft Jet database engine leaves a write lock on the first data page of the Customers table. Assuming that only one user is connected to the database, the write lock has a starting and ending lock address of 2000AC00 to 2000AD00 (Microsoft Jet 2.x). To determine what page is being locked, follow these steps:

  1. Remove the first digit (2000AC00 to AC00).

  2. Convert to decimal (AC00 to 44032).

  3. Determine the page number by dividing by 512 (44032 / 512 = 86). Thus, page 86 is being locked via the .LDB file by rs.Edit. Page 86 would represent the page in the database starting at 176,128 bytes and extending to 178,176 bytes.

To determine the user number that is locking the page, simply take the last two digits of the ending address, convert it to decimal, and add one digit if the database is Microsoft Jet 2.x (2000AD00 00 + 1 = 1). If the database is a Microsoft Jet 3.0 database, simply converting the last two digits to decimal gives the user number (2000A701 01 = 1). Thus, in the Microsoft Jet 2.x example above, user one would be holding the lock (last two digits are zero, plus one).

To find the computer name of the user, simply open the corresponding .LDB file and move to the offset of the user number * 64. In the previous example, the computer name would be in the first 32 bytes of the .LDB file (1 * 64). Alternatively, you can use the 32-bit LDBView utility included with this article to quickly find the computer name of the user who is placing the lock.

In addition to knowing which user has a page locked, it is usually more useful to know which table is associated with the write lock. You can find this information by using the 32-bit Visual Basic 4.0 utility (DBLock) that is included with this article.

Table 2 below shows the majority of locks placed from the previous code example with descriptions for each lock. By using DBLock, you can enter the beginning lock range shown and get a description of the type of lock and which table it is locking. DBLock cannot get table names for long value pages and other internal types of pages used by Microsoft Jet.

Table 2


DAO command
Microsoft Jet 2.x
lock hex

Description
Microsoft Jet 3.0 lock hex
Description
OpenDatabase 10000001–
10000001
User lock 10000001–
10000001
User lock
30001A01–
30001A01
Read lock on index page for MSysObjects
30001E01–
30001E01
Read lock on index page for MSysObjects
30002001–
30002001
Read lock on index page for MSysIndexes
30002201–
30002201
Read lock on index page for MSysACEs
30003001–
30003001
Read lock on index page for MSysObjects
3000AA01–
3000AA01
Read lock on index page for MSysColumns
30040C01–
30000C01
Read lock on index page for MSysColumns
30065201–
30065201
Read lock on index page for MSysACEs
OpenRecordSet 4000A601–
4000A601
Shared table-read lock on Customers table 4000A201–
400A201
Shared table-read lock on Customers table
5000A601–
5000A601
Shared table-write lock on Customers table
rs.Edit No lock placed due to optimistic locking
rs.Update 2000AC00–
2000AD00
Write lock on Customers table 2000A601–
2000A701
Write lock on Customers table.
Repeat loop until EOF 2000AE00–
2000AF00
15 rows have looped through before the next data page is write locked. Even though pessimistic locking is enabled, the transaction holds the write locks until WS.CommitTrans. 2000A801–
2200A901
16 rows have looped through before the next data page is write locked.
2000B000–
2000B100
2000AA01–
2000AB01
2000B200–
2000B300
2000AC01–
2000AD01
2000B400–
2000B500
2000AE01–
2000AF01
2000B600–
2000B700
2000B001–
2000B101
2000B800–
2000B900
2000B201–
2000B301

There is some interesting information in this trace log. Notice that the Microsoft Jet 3.0 log has no read locks placed and that the last two digits of the write locks represent the true user number, whereas the last two digits for the Microsoft Jet 2.x write locks require the user to add one to determine the true user number. Also, when optimistic locking is used, the explicit transaction holds write locks for the duration of the transaction, thus emulating pessimistic locking. Many users mistakenly believe that optimistic locking is always in effect regardless of the transaction mechanism. Another example of this would be to issue the following code to mimic the DAO code example above.

Internal Transactions on SQL DML Commands

Because all DML commands are implicitly wrapped in a transaction, the write locks in the example below are held until the UPDATE statement is completed successfully.

Sub WriteLocksDML ()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Set db = OpenDatabase("NWind.mdb", False, False)
    DBEngine.Execute _
        "UPDATE Customers SET ContactName = ContactName", _
        dbFailOnError
End Sub

Internal Transactions on DAO Code with Microsoft Jet 3.0

By removing the explicit calls to WS.BeginTrans and WS.CommitTrans from the code above (WriteLocksDatapage), Microsoft Jet 3.0 utilizes internal transactions. Based on settings (SharedAsyncDelay and MaxBufferSize) in the system registry, Microsoft Jet places a series of commands into an internal transaction. While this does speed up performance, it may also create concurrency issues where they did not exist in Microsoft Jet 2.x.

Nested Transactions

The following code example and lock log (Table 3) demonstrate that locks are held in nested transactions until the outermost transaction is committed:

Sub NestedTransactions()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim ws As Workspace
    Set ws = Workspaces(0)
    Dim ws1 As Workspace
    Set ws1 = Workspaces(0)
    ws.BeginTrans
    db.Execute _
        "UPDATE Suppliers SET [Contact Name] = [Contact Name]", _
        dbFailOnError
    ws1.BeginTrans
    db.Execute _
        "UPDATE Employees SET [Last Name] = [Last Name]", _
        dbFailOnError
    ws1.CommitTrans
    ws.CommitTrans
End Sub

Table 3


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
WS.BeginTrans Start outer level transaction. Start outer level transaction.
UPDATE Suppliers 40042401–
40042401
Shared table-read lock on Suppliers table 400C7E01–
400C7E01
Shared table-read lock on Suppliers table.
50042401–
50042401
Shared table-write lock on Suppliers table 200C5001–
200C5101
Write lock on data page for MsysObjects. This is for compilation of the temporary query and is not placed every time.
20042600–
20042700
Write lock on data page for Suppliers table 200C8401–
200C8501
Write lock on data page for Suppliers table
20042800–
20042900
Write lock on data page for Suppliers table 200C8601–
200C8701
Write lock on data page for Suppliers table
20042A00–
20042B00
Write lock on data page for Suppliers table
WS1.BeginTrans Start inner level transaction Start inner level transaction.
UPDATE Employees 4000CC01–
4000CC01
Shared table-read lock on Employees table 4000C801–
4000C801
Shared table-read lock on Employees table.
5000CC01–
5000CC01
Shared table-write lock on Employees table
2000E600–
2000E700
Write lock on data page for Employees table 2000E401–
2000E501
Write lock on data page for MSysObjects. This is for compiling the temp query.
20020400–
20020500
Write lock on data page for Employees table 20020801–
20020901
Write lock on data page for Employees table
WS1.CommitTrans Write locks stay on. Write locks stay on.
WS1.Rollback If a rollback is issued here, the write locks on the Employees table are removed. If a rollback was issued here, the write locks on the Employees table would be removed.
WS.CommitTrans Write locks are removed. Write locks are removed.

The previous example illustrates a couple of key points. If a CommitTrans or Rollback statement is not issued or is not balanced with corresponding BeginTrans commands, write locks are held until the database is closed. This can cause serious concurrency issues. If a user does not explicitly check the error status of a Rollback or CommitTrans statement, then the command could fail, leaving locks on the .LDB file. Also, simply executing a nested CommitTrans statement does not remove write locks nor is the modified data actually flushed to disk until the outermost transaction is committed.

Index Page Write Locks

Below is sample code and a lock log (Table 4) of the majority of write locks placed on both data and index pages.

Sub WriteLocksIndexPages ()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Set db = OpenDatabase("NWind.mdb", False, False)
    Dim CompanyName As String
    Set rs = db.OpenRecordset _
        ("SELECT * FROM Customers", dbopendynaset)
    'The constant dbFreeLocks is DAO v3 specific and is used
    'when Jet 3.0 (Access 7.0/ VB 4.0) is opening a Jet 2.x database.
    'If Access 2.0 or VB 3.0 is used, the constant would be db_FreeLocks.
    DBEngine.Idle dbFreeLocks 'Works only in Jet 2.x.
    Set ws = Workspaces(0)
    ws.BeginTrans
    While Not rs.EOF
        rs.Edit
        CompanyName = rs![Company Name]
        rs![Company Name] = CompanyName
        DBEngine.Idle dbFreeLocks 'Works only in V2.
        rs.Update
        rs.MoveNext
    Wend
    ws.CommitTrans
    rs.Close
End Sub

Table 4


DAO Command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
OpenDatabase 10000001 User lock 10000001 User lock
30001A01–
30001A01
Read lock on index page for MSysObjects
30001E01–
30001E01
Read lock on index page for MSysObjects
30002001–
30002001
Read lock on index page for MSysIndexes
30002201–
30002201
Read lock on index page for MSysACEs
30003001–
30003001
Read lock on index page for MSysObjects
3000AA01–
3000AA01
Read lock on index page for MSysColumns
30040C01–
30000C01
Read lock on index page for MSysColumns
30065201–
30065201
Read lock on index page for MSysACEs
OpenRecordSet 4000A601–
4000A601
Shared table-read lock on Customers table 4000A201–
4000A201
Shared table-read lock on Customers table
5000A601–
5000A601
Shared table-write lock on Customers table
DBFreeLocks All 30 million hex locks removed Does nothing
rs.Edit 2000AC00–
2000AD00
2000A601–
2000A701
rs.Update 3000BC01–
3000BC01
Read lock on index page for Customers table
3000BE01–
3000BE01
Read lock on index page for Customers table
DBFreeLocks 3000BE01 is removed; 3000BC01 remains and is not freed by a DBFreelocks statement. Does nothing
rs.Update 2000AE00–
2000AF00
Write lock on data page for Customers table 2000A801–
2200A901
Write lock on data page for Customers table
2000BE00–
2000BF00
Write lock on index page for Customers table 2000B601–
2000B701
Write lock on index page for Customers table
Continue looping
2000B000–
2000B100
Write lock on data page for Customers table 2000AA01–
2000AB01
Write lock on data page for Customers table
2000B200–
2000B300
Write lock on data page for Customers table 2000AC01–
2000AD01
Write lock on data page for Customers table
2000B400–
2000B500
Write lock on data page for Customers table 2000AE01–
2000AF01
Write lock on data page for Customers table
2000B600–
2000B700
Write lock on data page for Customers table 2000BA01–
2000BB01
Write lock on index page for Customers table
2000B800–
2000B900
Write lock on data page for Customers table 2000B001–
2000B101
Write lock on data page for Customers table
3000C001–
3000C001
Read lock on index page for Customers table 2000B201–
2000B301
Write lock on data page for Customers table
2000C000–
2000C100
Write lock on index page for Customers table

The above log illustrates the reduction in read locks from Microsoft Jet 2.x to Microsoft Jet 3.0. Most importantly, the lock log demonstrates how indexes can greatly affect concurrency. In both versions of Microsoft Jet, there were three index pages that were locked in the Customers table that referenced eight data pages. Thus, by updating one index column, a user could be locking out many other users trying to update values on different data pages.

Note   While indexes can increase retrieval performance, it is very important to weigh their costs when using a multiuser system. By following the motto "If in doubt, index," you may experience substantial performance degradation and concurrency conflicts.

Coming up with the best balance of indexed and nonindexed columns is an art and is different for every application and database. One rule of thumb: Don't index columns that have a high duplication factor—for example, indexing a Customer Type column on the Customers table where there were only four unique Customer Types in the Customers table that had 100,000 rows. Indexing this column would typically not increase performance on SQL SELECT statements and would cause performance (maintaining the index) and concurrency issues (each highly duplicated index page would reference many data pages) on SQL DML statements. The developer and system administrator need to weigh the importance of retrieval time (SQL SELECT statements) versus online transaction processing (OLTP) time (SQL DML statements) and come up with a proper balance of indexes that provides the fastest retrieval times with the fastest OLTP times.

Inserting Rows

The following code example and lock log (Table 5) illustrate the drastic improvement in the locking algorithms used in Microsoft Jet 3.0. While Microsoft Jet 2.x would require a lock on the last data page and the TBH during a row insert, Microsoft Jet 3.0 requires a lock only on the last data page. Also, if Microsoft Jet 3.0 can't acquire a lock on the last data page, it continues seeking available data pages allocated to that table until it can successfully acquire a lock. This eliminates locking contentions when multiple users insert rows into the same table at the same time. The exception to this is when indexes are present on the table, because the user might experience some conflicts while modifying the index page.

Also note the keyword dbFailOnError after the SQL string. This is absolutely necessary if a user wants to have an error returned. This was not supported in Visual Basic 3.0, and it prevented many users from using SQL in multiuser environments because locking conflicts could not be trapped.

Sub InsertRows ()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    db.Execute _
        "INSERT INTO Shippers ([Company Name]) VALUES ('Test')", _
        dbFailOnError
End Sub

Table 5


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
INSERT 20041A00–
20041B00
Write lock on table header page 2003BE01–
2003BF01
Write lock on last data page
20041C00–
20041D00
Write lock on last data page of Shippers table 2003C401–
2003C501
Write lock on PK index page for counter column
20041E00–
20041F00
Write lock on PK index page for counter column in Shippers table 4003BA01–
4003BA01
Table-read lock on Shippers table
30041E00–
30041E00
Read lock on PK index page for counter column in Shippers table 5003BA01–
5003BA01
Table-write lock on Shippers table
40041A01–
40041A01
Table-read lock on Shippers table
50041A01–
50041A01
Table-write lock on Shippers table

Inserting Rows with a Counter

The following code example and lock log (Table 6) illustrate some of the types of locks placed when inserting rows with a Counter data type.

Sub InsertCounter()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Set ws = Workspaces(0)
    Dim SQLStr AS String
    SQLStr = "INSERT INTO Categories ([Category Name], Description, "
    SQLStr = SQLStr & "Picture) VALUES ('CounterLock', 'CounterLock', NULL)"
    ws.BeginTrans
    db.Execute SQLStr, dbFailOnError
    ws.CommitTrans
End Sub

Table 6

DAO command Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
INSERT 20003C00–
20003D00
Write lock on TBH page for Categories table 20004601–
20004701
Write lock on data page for Categories table
20004A00–
20004B00
Write lock on data page for Categories table 20009C01–
20009D01
Write lock on index page for Categories table
2000A000–
2000A100
Write lock on index page for Categories table 20009E01–
20009F01
Write lock on index page for Categories table
2000A200–
2000A300
Write lock on index page for Categories table
40003C01–
40003C01
Shared read lock on Categories table 40003401–
40003401
Shared read lock on Categories table
50003C01–
50003C01
Shared write lock on Categories table 50003401–
50003401
Shared write lock on Categories table

Deleting Rows

The following code example and lock log (Table 7) demonstrate the types of locks placed when making a deletion.

Sub DeleteRow()
    'This code example is intended to be run from Access 7.0 or VB 4.0.
    Set ws = Workspaces(0)
    ws.BeginTrans
    db.Execute _
    "DELETE ROWS FROM Customers WHERE [Customer Id] = 'Paris'", _
        dbFailOnError
    DBEngine.Idle dbFreeLocks
    ws.CommitTrans
End Sub

Table 7

DAO
command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
DELETE 20003400–
20003500
Write lock on data page for table MSysObjects 20002E01–
20002F01
Write lock on data page for table MSysObjects
2000A600–
2000A700
Write lock on TBH page for Customers table 2000AC01–
2000AD01
Write lock on data page for Customers table
2000B400–
2000B500
Write lock on data page for Customers table 2000B401–
2000B501
Write lock on index page for Customers table
2000BA00–
2000BB00
Write lock on index page for Customers table 2000B601–
2000B701
Write lock on index page for Customers table
2000BE00–
2000BF00
Write lock on index page for Customers table 2000B801–
2000B901
Write lock on index page for Customers table
2000C200–
2000C300
Write lock on index page for Customers table 2000BC01–
2000BD01
Write lock on index page for Customers table
2000C400–
2000C500
Write lock on index page for Customers table 2000BE01–
2000BF01
Write lock on index page for Customers table
200CE201–
200CE301
Write lock on data page for table MsysObjects
200CE401–
200CE501
Write lock on long value page for temporary query
3000BA01–
3000BA01
Read lock on index page for Customers table
3000BE01–
3000BE01
Read lock on index page for Customers table
3000C201–
3000C201
Read lock on index page for Customers table
3000C401–
3000C401
Read lock on index page for Customers table
4000A601–
4000A601
Shared read lock on Customers TBH 4000A201–
4000A201
Shared read lock on Customers TBH
5000A601–
5000A601
Shared write lock on Customers TBH 5000A201–
5000A201
Shared write lock on Customers TBH
400B8A01–
400B8A01
Shared read lock on Orders TBH

Updating Rows in a Multitable Join

The following code example and lock log (Table 8) illustrate that write locks are placed on all tables involved in the join, even when only one column from one table is being modified.

Sub UpdateJoin ()
    Dim SQLStr As String
    Dim ContactName As String
    SQLStr = "SELECT * FROM Customers AS C INNER JOIN "
    SQLStr = SQLStr & "(Orders AS O INNER JOIN "
    SQLStr = SQLStr & "[Order Details] AS OD ON "
    SQLStr = SQLStr & "OD.[Order Id] = O.[Order Id]) ON "
    SQLStr = SQLStr & "C.[Customer Id] = O.[Customer Id] "
    Set rs = db.OpenRecordset(SQLStr, dbOpenDynaset)
    rs.Edit
    ContactName = rs![Contact Name]
    rs![Contact Name] = ContactName
    rs.Update
    rs.Close
End Sub

Table 8


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
OpenRecordSet 4000A601–
4000A601
Shared table-read lock on Customers table 4000A201–
4000A201
Shared table-read lock on Customers table
40026201–
40026201
Shared table-read lock on Order Details table 40024801–
40024801
Shared table-read lock on Order Details table
40031C01–
40031C01
Shared table-read lock on Orders table 40020C01–
40020C01
Shared table-read lock on Orders table
5000A601–
5000A601
Shared table-write lock on Customers table
50026201–
50026201
Shared table-write lock on Order Details table
50031C01–
50031C01
Shared table-write lock on Orders table
rs.Edit 2000AC00–
2000AD00
Write lock on data page for Customers table 2000A601–
2000A701
Write lock on data page for Customers table
2002A000–
2002A100
Write lock on data page for Order Details table 20029801–
20029901
Write lock on data page for Order Details table
2037C00–
2037D00
Write lock on data page for Orders table 200365401–
20035501
Write lock on data page for Orders table
rs.Update All write locks removed. All write locks removed.

Deciphering Read and Commit Locks (Microsoft Jet 2.x)

As stated previously, read and commit locks only occur in the 30-million-hex range. Read locks always start and end on the same byte and commit locks start and end between 256 and 512 bytes. To determine what page is being locked, use the 32-bit Visual Basic 4.0 DBLock program included with this article. To determine the user number, simply take the last two digits of the ending lock range and convert them to decimal form

Deciphering Read and Commit Locks (Microsoft Jet 3.0)

To determine which page is being locked, use the DBLock program. To determine the user number, simply take the last two digits of the ending lock range and convert them to decimal.

Following is a code sample and lock log (Table 9) of read locks that were placed while reading a picture from the NWIND database:

Sub ReadLocks ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim Photo As Variant
    Dim db As Database
    Dim rs As RecordSet
    Set Db = OpenDatabase("NWIND.MDB", False, True)
    Set rs = db.OpenRecordset _
        ("SELECT * FROM Employees", dbOpenDynaset)
    While Not rs.EOF
        Photo = rs!Photo
        DBEngine.Idle DBFreeLocks
        rs.MoveNext
    Wend
    rs.Close
End Sub

Table 9


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
OpenDatabase 10000001–
10000001
User lock 10000001–
10000001
User Lock
OpenRecordSet 4000CC01–
4000CC01
Shared table-read lock 4000C801–
4000C801
Shared table-read lock
5000CC01–
5000CC02
Shared table-write lock
Photo = RS!Photo 3000D001–
3000D001
Read lock on long value. 20063801–
20063801
Read lock on long value.
DBEngine.Idle DBFreeLocks Lock is removed. Lock is persistent until RS.MoveNext. DBFreeLocks is an obsolete command in Microsoft Jet 3.0.
Repeat loop until EOF 3000E801–
3000E801
Read lock on next long value. 2000E601–
2000E601
Read lock on next long value.
30010001–
30010001
" " 2000FC01–
2000FC01
" "
30011801–
30011801
" " 20011201–
20011201
" "
30013001–
30013001
" " 20012801–
20012801
" "
30014801–
30014801
" " 20013E01–
20013E01
" "
30016001–
30016001
" " 20015401–
20015401
" "
30017801–
30017801
" " 20016301–
20016301
" "
30018E01–
30018E01
" " 20018201–
20018201
" "
3001A401–
3001A401
" " 20019801–
20019801
" "
3001BC01–
3001BC01
" " 2001AE01–
2001AE01
" "
3001D401–
3001D401
" " 2001C401–
2001C401
" "
3001EC01–
3001EC01
" " 2001DA01–
2001DA01
" "
30020601–
30020601
" " 2001F201–
2001F201
" "
30021E01–
30021E01
" " 20020A01–
20020A01
" "

Deciphering All Types of Table Locks

As stated previously, table locks occur in three ranges: 40, 50, and 60 million hex. Using the Customers table as an example, and assuming that the user below is the first user to connect to the database in shared mode, the following steps determine which table is being locked:

  1. Open the Customers table with the OpenRecordSet method, using the shared table-read lock at 4000A601 as a reference.

  2. Drop the first digit (4000A601 becomes A601).

  3. Drop the last two digits (A601 becomes A6).

  4. Divide by two (A6 hex / 2 hex = 53 hex) and convert to decimal (83) to get the tableid.

  5. Browse MSysObjects.Id for value 83, and then look at MSysObjects.Name in the rows identified to determine the table name.

The same information can be obtained by using the 32-bit Visual Basic 4.0 utility included with this article.

Below is the lock log (Table 10) for the following commands:

Sub TableDenyRead ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Set db = OpenDatabase("NWind.mdb", false, false)
    Set rs = db.OpenRecordset _
        ("Customers", dbOpenTable, dbDenyRead)
End Sub

Table 10


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
OpenDatabase 10000001–
10000001
User lock 10000001–
10000001
User lock
OpenRecordSet 4000A600–
4000A700
Exclusive table-read lock 4000A201–
4000A301
Exclusive table-read lock

Below is the lock log (Table 11) for the following commands:

Sub TableDenyWrite ()
    'This example is intended to be run from Access 7.0 or VB 4.0.
    Dim db As Database
    Dim rs As Recordset
    Set db = OpenDatabase("NWind.mdb", false, false)
    Set rs = db.OpenRecordset _
        ("Customers", dbOpenTable, dbDenyWrite)
End Sub

Table 11


DAO command
Microsoft Jet 2.x lock hex
Description
Microsoft Jet 3.0 lock hex
Description
OpenDatabase 10000001–
10000001
User lock 10000001–
10000001
User lock
OpenRecordSet 4000A601–
4000A601
Shared table-read lock 4000A201–
4000A201
Shared table-read lock
5000A601–
5000A601
Shared table-write lock 5000A201–
5000A201
Shared table-write lock
6000A601–
6000A601
Table deny-write lock 6000A201–
6000A201
Table deny-write lock

Using the Utilities

All the utilities referenced in this paper are included with this article.

LDBView

You can use the LDBView utility (Figure 1) to accomplish the following tasks:

Figure 1

LDBView is a 32-bit single .EXE utility that can run under either Windows 95 or Windows NT and can view both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. In order for the utility to run, a corresponding .LDB file must be present.

To use the utility, click the database that you want to view from the File menu. After selecting a database, a screen similar to that in Figure 1 appears. At this point, you can modify the refresh interval by selecting values on the View menu. Changing these values determines how often LDBView looks at and reads and displays information from the selected database and corresponding .LDB file.

DBLock

The DBLock utility (Figure 2) determines which types of locks that Microsoft Jet places, which page types are being locked, and which tables are associated with those pages. By using a utility, such as NetWare's Monitor utility, you can immediately enter a beginning lock range and determine what effect a particular command would have on other users. You can also use this utility to help determine what bottlenecks exist when users experience locking conflicts. You can then use this information to modify the application or database design (by removing unnecessary indexes, for example).

Figure 2

DBLock is a 32-bit Visual Basic 4.0 utility that runs under either Windows 95 or Windows NT and can work against both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. The Setup program installs the Microsoft Jet DLLs necessary to run this program. By default, DBLock installs a default SYSTEM.MDA (security file for Microsoft Jet 2.x) and a default SYSTEM.MDW (security file for Microsoft Jet 3.0). If you have an unsecured database, the utility grants rights to MSysObjects for user Admin. If you have a secured database, you need to use the Tools menu to point to the path of your security database.

After a database has been selected, simply enter in the hexadecimal values that appear (on the NetWare Monitor utility, for example) and the information for the table name, page type, available page space, and type of lock is populated.

MSLDBUSR.DLL

This 32-bit DLL enables you to retrieve one of the most often-sought pieces of information: a list of users connected to the database. This was previously impossible to do programmatically unless a programmer understood the user lock algorithm and understood how to place extended byte range locks. Because this is a 32-bit DLL, it works only with 32-bit products (such as Microsoft Access 7.0, Visual Basic 4.0, Microsoft Excel 7.0, and Visual C++ฎ) running on Windows 95 or Windows NT. However, it retrieves information for both Microsoft Jet 2.x and Microsoft Jet 3.0 databases. MSLDBUSR.DLL enables your application to display a list of users who are preventing you from opening the database exclusively or running a repair or a compact.

Because the DLL places real-time extended byte range locks, the information that it retrieves can be out of date as soon as another user logs out, but recalling the DLL always provides current information.

Figure 3 is a screen shot of a sample Visual Basic 4.0 utility (LDBUsrDLL.VBP) that includes source code on how to use the DLL. Figure 4 shows a screen shot of a sample Microsoft Access 7.0 database (provided by Michael Kaplan).

Figure 3

To use MSLDBUSR.DLL, simply select a database file and choose an option under LDB User Options. Then click Execute or Refresh, which calls the function LDBUser_GetUsers. To view error codes and descriptions, click the Show Viewer button, which calls the LDBUser_GetError function. This utility does not work against library or wizard databases, because those are opened exclusively and the Show Users That Left Database in a Suspect State option needs to open the database in a shared mode.

Figure 4

The above screen shot uses the LDB700.MDB database from Microsoft Access 7.0. For the utility to work properly, it is necessary to have the MSLDBUSR.DLL in the path or the SYSTEM directory.

Note   To use the DLL you must make the following two function declarations. These must go into your module (this cannot be a form module), and the DLL must be located in the SYSTEM directory.

Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL"
    (lpszUserBuffer() As String, ByVal lpszFilename As String,
       ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL"
       (ByVal nErrorNo As Long) As String

The following options can be called from the function LDBUser_GetUsers:

Public Const OptAllLDBUsers &H1 Returns a list of users in the .LDB file. This option opens the .LDB file and returns each user one by one and then adds him/her to the array list. This option does not check to see whether the users currently have the database open. It essentially represents users who at one time or another had the database open. It also does not represent all users who have ever had the database open.
Public Const OptLDBLoggedUsers &H2 Shows all .LDB users who currently have the database open. This is similar to OptAllLDBUsers, except it checks to make sure that the users listed in the .LDB file have the database opened. Often, OptAllLDBUsers and OptLDBLoggedUsers show the same list.
Public Const OptLDBCorruptUsers &H4 Shows all user(s) who left the database in a suspect state.
Public Const OptLDBUserCount &H8 Returns a list of users connected to the database. (Note that you still must include an array and .LDB filename.)
Public Const OptLDBUserAuthor &HB0B Invokes the credits screen when using -Z as the .LDB filename.

Here is some sample Visual Basic for Applications code that gets the first user in the .LDB file:

    'The DLL must be in the SYSTEM directory or have a path referencing it.
Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" _
    (lpszUserBuffer() As String, ByVal lpszFilename As String, _
    ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL" _
    (ByVal nErrorNo As Long) As String
Public Const OptAllLDBUsers = &H1
Public Const OptLDBLoggedUsers = &H2
Public Const OptLDBCorruptUsers = &H4
Public Const OptLDBUserCount = &H8
Public Const OptLDBUserAuthor = &HB0B
Sub Main()
    'It is important that ReDim is used to define the array as the DLL,
    'because the DLL depends on being able to redimension the array.
    ReDim msString(1) As String
    'The array is 1-based rather then 0-based, regardless if Option Base 1
    'is specified in the declarations section.
    Dim miLoop As Integer
    Dim LDBReturn As String
    Dim LDBName As String
    LDBName = InputBox _
        ("Enter a LDB name", " ", "c:nwind.mdb")
    miLoop = LDBUser_GetUsers(msString, LDBName, OptAllLDBUsers)
    'The function calls cannot be combined and must be used individually.
    'Get the first user in the selected .LDB file.
    LDBReturn = msString(1)
    MsgBox ("Total number of users in .LDB file is: " & miLoop & _
        ". The first computer name in .LDB file is " _
        & LDBReturn & ".")
End Sub