Kevin Collins
Microsoft Jet Program Management
November 21, 1995
Click to open or copy the utilities discussed in this technical article.
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:
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 fileno 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.
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.
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 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 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, 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:
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.
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 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 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 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.
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 |
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.
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.
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.
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:
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.
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
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.
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.
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 factorfor 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.
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 |
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 |
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 |
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. |
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
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 |
" " |
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:
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 |
All the utilities referenced in this paper are included with this article.
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.
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.
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