Understanding Jet Database Files

The traditional name for an .mdb file that stores Access application, data objects, or both is Access database. As other database programming tools, such as Visual Basic, began using data-only .mdb files, Jet database became the preferred designation for files containing only tables and queries. Access is only one of many Microsoft applications and programming tools that take advantage of the Jet database engine. Jet 4.0, which Access 2000 and 2002 also use, is the latest and last Jet version. Access 2002 was the first Access upgrade that didn't introduce a new Jet version or require converting existing .mdb files into a new format to enable changing the design of database objects. Access 2003's default database version remains Access 2000 format.

Microsoft's determination to make SQL Server the database engine of choice for Access 2003 and all future Access versions is another reason for changing from Access to Jet terminology for applications that use .mdb and related files. Access 2000 and 2002 store application objects forms, reports, macros, and modules in a new compound file format called a DocFile. Conventional Access applications store the application object DocFile within the .mdb file. Access Data Projects (ADP), which now represent the preferred approach to designing Access applications that connect to SQL Server databases, store the DocFile directly on disk as an .adp file. Combining ADP front ends with SQL Server back-end databases eliminates the need to periodically compact .mdb files and occasionally repair corrupted Jet databases. The chapters of Part V, "Upgrading to SQL Server Databases," cover designing ADP and SQL Server databases.

Tip

Using ADP and SQL Server databases for simple, single-user Access applications is overkill. You don't need the power of SQL Server 2000 for mailing list, contact management, or similar projects. Creating applications that use Jet to store your data is easier than designing and managing SQL Server databases. Thus, the beginning chapters of this book deal exclusively with Jet databases.

If you intend to create multiuser applications, which let several users update the database simultaneously, seriously consider using Access's no-charge version of SQL Server 2000 Desktop Engine (MSDE) to store the data. Use SQL Server for any databases whose content is vital to the continued success or existence of an organization, such as sales orders, invoices, and accounts receivable. Access 2003 includes an Upsizing Wizard that greatly simplifies moving from Jet to SQL Server databases. So, you can start with Jet and then move to SQL Server as you become proficient in database application design.

References in earlier chapters to the advantages of Jet's single-file approach to database storage don't take into account the other types of files associated with Jet databases. Access requires a workgroup information file to open an .mdb file and automatically creates record-locking information files for open databases. The following sections briefly describe the additional types of files used by conventional Access (Jet) applications.

Jet Workgroup Information Files

In addition to database files with the .mdb extension, Access uses a default security database file called the workgroup file and named System.mdw. In early versions of Access, this file was named System.mda and called the system file. The location of System.mdw depends on your operating system. Windows XP, the operating system used for most of the examples of this book, and Windows 2000 store System.mdw in the \Documents and Settings\LogonID\My Documents\Application Data\Microsoft\Access folder. The LogonID folder name is the logon you used when installing Office 2003 on your computer usually the local Administrator account. If you're a member of a Windows NT/2000+ domain, the LogonID folder is DOMAIN.UserName.

Tip

To determine the location of System.mdw on your computer, open a database, and choose Tools, Security, Workgroup Administrator to open the Workgroup Administrator dialog, which displays the local path to the file.

System.mdw contains information about the following:

• Names of users and groups of users who can open a Jet database. Access's default username is Admin with an empty password, which lets you open Jet databases without providing a username and password in a logon dialog.

• User passwords and a unique binary code, called a Security ID (SID), that identifies the current user to Access.

• An optional Workgroup ID (WID) that uniquely identifies the workgroup. The default System.mdw file doesn't have a WID.

The default System.mdw file is the same for every initial Access installation. Access runs and lets you open unsecured databases without having System.mdw on your machine.

Caution

If you decide to secure your Admin account with a password or apply security to database objects in an .mdb file, make a backup of System.mdw or another .mdw file you create with the Workgroup Administrator and save it in a secure location. If you delete your workgroup file or it becomes corrupted, you won't be able to open secure .mdb files.

Jet Record-Locking Information Files

When you open an .mdb or other types of Access-related Jet files, Jet automatically creates a record-locking file having the same name as the database but with an .ldb extension. The purpose of the .ldb file is to maintain for multiuser applications a list of records that each user currently is updating. The .ldb file prevents data corruption when two or more users simultaneously attempt to change data in the same record. The presence of an .ldb file also prevents two or more users from saving design changes to the same database. If you open a database that another user has open with an object in design mode, you receive a message that you can't save any changes you make. The same restriction applies if you have the same database open in two instances of Access. When all users or instances of Access close the .mdb file, Jet deletes the .ldb file.

Jet Add-In (Library) Databases

Another category of Access database files is add-ins, also called libraries. Add-ins are Access databases usually with an .mda or .mde extension to distinguish them from user databases that you can link to Access by choosing Tools, References in the VBA editor's menu, or through the Add-In Manager, which you access by choosing Tools, Add-Ins from Access's main menu. The Add-In Manager also lets you add another class of extensions to Access called COM Add-ins.

When you link an Access add-in, all elements of the library database are available to you after you open Access. The Access 2003 wizards that you use to create forms, reports, graphs, and other application objects are stored in a series of Access add-in database files: Acwzlib.mde, Acwztool.mde, and Acwzmain.mde. Access 2003 also installs add-in data files, which have an .mdt extension. The standard Access wizards don't appear in the Add-In Manager's dialog. Add-in databases are an important and unique feature of Access; third-party firms provide useful libraries to add new features and capabilities to Access.