This article provides an introduction to the issues surrounding maintaining an Access database.

Compacting a Database

All your data in a database starts out with the records in each table laid out in a sequence one after another. This applies to both SQLServer and Access. The order of the records is controlled normally by the primary key field, although you may come across the term "CLUSTERED INDEX", which is the name of the index (often the primary key) used to control the order of the data records.

Over a period of time as new records get added, or existing records changed, the records no longer follow the original efficient sequence. So on a regular basis we "Compact" the Access database, or run a maintenance plan for a SQLServer database to re-establish the efficient ordering of the data. This is similar to the ideas behind "Disk Defragmentation".

How often to do this depends on the amount of change being made to the data. It would not be unusual to compact a large access database once a week, and a small access database every few months.

Repairing a Database

Because each user sharing an access database has their own copy of the JET Database Engine running in their own memory, it is possible for these copies to get out of synchronisation. When this happens (and for other reasons) a database can become corrupt. Corruption is more likely when you have large databases shared by a large number of users.

In most cases database corruption can be recovered without any loss of data. If you imagine a database being a bit like a library, then corruption is where the library books (data) normally remain intact, but the reference catalogues (Indexes) can become damaged. One suggested last resort strategy often overlooked is to use a more recent version of Access to repair an older version's corrupt database.

A good tutorial discussing the reasons behind database corruption can be found at Access Database Corruption Repair Guide.

For Access 2000 you should also take a look at ACC2000: Jet Compact Utility Available in Download Center where you can download the JET Compact utility.

There is a know problem with databases that relates to a subject called oplocks. If having upgraded servers or your version of office you are having problems with database corruptions then it is worth looking into. To resolve this problem in a large organisation is not straightforward as it may mean reconfiguring systems.

Top of Page

Opportunistic locking, also known as oplocks

Opportunistic locking may increase the risk of Jet database corruption when a file is shared by two or more clients on a network file server. This problem applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This problem also applies to clients that are running Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows XP and that connect to a file server that supports opportunistic locking.

See also How to keep a Jet 4.0 database in top working condition.

Top of Page

Issues Relating To The Locking LDB File

From Access 97 forward the locking file which is created in the same directory as your database, databasename.ldb, get created and destroyed dynamically as users interact with the system. When the last user disconnects the file is removed, and when the first user starts the application the file is created. This means that users require appropriate permissions on the application directory to create and delete files.

When a database becomes corrupted, some users may still be working in the database. Before you can repair or compact the database you must get all users out of the database and ensure that the locking file is gone.

In a large organisation, even with the best will you may on some occasions need to get your operations people to delete the file, but in most cases you just have to get all users out of the system. The way in which you find out who is in the system is by reading the .LDB file using some program code, or you can also use the Jet User Roster. Stopping users entering the system is normally achieved by having the start-up form examine a table to see if a flag to eject users has been set.

Below is a very simple piece of code which you can use to examine the ldb file, more technical approaches are described in the links following the code.

' extract information from the LDB file for all users
Dim ldbname As String
Dim dbs As DATABASE
Set dbs = DBEngine(0)(0)
ldbname = Left(dbs.Name, (Len(dbs.Name) - 4)) & ".ldb"

' open the dos file
Dim ldbfilehandle As Long
Dim ldbfileinfo As String
' get next available file number
ldbfilehandle = FreeFile
If Dir(ldbname) <> "" Then
' find all active users
Open ldbname For Input As ldbfilehandle

Do While Not EOF(ldbfilehandle)
Line Input #ldbfilehandle, ldbfileinfo
MsgBox ldbfileinfo, , "Locking File Information"
Loop
Close ldbfilehandle

Else
MsgBox "Exclusive & Local", , "This database has been opened"
End If

For a method using ADO and the Jet UserRoster try : How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000.

For a method using DLL's try : ACC97: How to Read .LDB Files.

Top of Page

Decompile A Database

Inside your Access database you may have VBA code that you have written. In general you wish to keep the code in the database compiled. However, either to get around issues surrounding corruption, or to shrink you database during compaction you may want to decompile the VBA code.

This can only be achieved by having an icon to start Access with a special /decompile switch added to the command line. For an example of the syntax required here take a look at Access Database Corruption Repair Guide. (this is an excellent article with lots of good links to other articles).

Top of Page

Tracking Users

It is also useful to track users, as this can assist with identifying potential sources of corruption, and in knowing who is possibly in the system. Whilst access offers functions such as CurrentUser, this is often insufficient as many users may be logged into the application with the same access user name. One solution is to force users to login through your own table driven list of user names, another solution is to utilise calls to the operating system such as the one shown below.

Global NTUserId As Long
Declare Function CSM_GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nsize As Long) As Long

Function Login_GetUserName() As String
Dim USER As String
USER = Space(255)
If CSM_GetUserName(USER, Len(USER) + 1) <> 1 Then
Login_GetUserName = ""
Else
USER = Trim$(USER)
USER = Left(USER, Len(USER) - 1)
Login_GetUserName = USER
End If
End Function

Top of Page

Can't Live With Corruption Problems?

If you can not solve your database corruption issues then the best suggestion is to upsize the back-end data into SQL Server. SQL Server is fundamentally different from Access in so far as there is only one SQL Server engine running (on the server), so all traffic is co-ordinated through a single point. This means you are not subject to the problems leading to database corruption.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz