Data Access Technologies

Office 2000 and Database Engines

A database engine is the component of an application that provides the link between an application and its data. Microsoft Office 2000 installs Microsoft Jet version 4.0 as its primary database engine. Microsoft Access is the database application in Office 2000, and it relies on the Microsoft Jet database engine to provide data access services for Access database files (MDB files).

Because other Office 2000 applications can interact with data stored in Access database files, the applications also use the Jet database engine to retrieve data. For example, Microsoft Excel can retrieve data from Access databases and a variety of other data sources by creating a database query with the New Database Query command (Data menu, Get External Data submenu). When doing so, Excel also relies on the Jet database engine.

All Office applications that support Visual Basic for Applications (VBA) or Visual Basic Scripting Edition (VBScript) can use either the ADO programming model or the DAO programming model to retrieve and work with data that the Jet database engine manages from VBA code or VBScript script.

In addition to installing the Jet database engine, Office 2000 includes a separate program to install the Microsoft Data Engine (MSDE). MSDE is a new technology that provides local data storage compatible with Microsoft SQL Server version 7.0. Access 2000 provides tools to create and design databases that are stored by using MSDE or by using Microsoft SQL Server 6.5 or later. Access also creates a new file type called an Access project (ADP file) that stores the user interface elements, such as forms and reports that are used to work with MSDE or Microsoft SQL Server databases.

Top

Microsoft Jet database engine

Access 2000 and the other Office 2000 applications rely on the Jet database engine to provide data access services to the Access database file format. The Jet database engine also provides access to data that is stored in tables in a variety of formats, including the following:

• Microsoft Excel workbooks
• Microsoft Outlook folders
• Microsoft SQL Server databases
• HTML tables
• dBASE files
• Lotus 1-2-3 spreadsheets
• Tabular text files

Toolbox   The Office Resource Kit includes information about the data formats and drivers that are supported by the Jet database engine, as well as other data access components that are installed by Office, in a Word document named Formats.doc. For more information about installing this document, see Office Information.

The following sections describe the many improvements and new features in Microsoft Jet version 4.0.

Unicode support for character data

All character data that is stored in the Text and Memo fields in Access are now stored in the Unicode two-byte character representation format. Unicode storage replaces ANSI character sets and the Multibyte Character Set (MBCS) format used in previous versions of the Microsoft Jet database engine to store character data for languages such as Japanese and Chinese.

To accommodate the change to Unicode, and to enable all existing data to be converted successfully, page size (the internal unit of storage) is increased from 2 kilobytes (KB) to 4 KB. This larger page size allows an increase in the maximum database size from 1.07 gigabytes (GB) to 2.14 GB. Although the Unicode representation of character data requires more space to store each character (two bytes instead of one byte), columns with string data types can be defined to automatically compress the data, whenever it is possible.

Support for standardized sorting

The Unicode representation for the storage of character data enables the Microsoft Jet database engine to use a new sorting mechanism that is based on the native Microsoft Windows NT sorting functionality. This sorting mechanism uses the same locale Ids (LCID), and supports all Windows NT 4.0 and Windows 2000 sort orders.

Microsoft Jet uses the new sorting mechanism when running on all supported versions of Windows — Windows 2000, Windows NT 4.0, and Windows 95/98. This sorting mechanism standardizes sorting and ensures sorting consistency across operating systems. Microsoft SQL Server 7.0 and Microsoft Visual Basic 6.0 also use the sorting mechanism to provide cross-product consistency.

Compatibility with SQL Server data types

To make it easy to upsize Access databases to Microsoft SQL Server databases, and for better compatibility between Microsoft Jet database engine and Microsoft SQL Server replication, data types used by the Jet database engine are more closely aligned with Microsoft SQL Server data types. In some instances, this data type consistency also provides greater compatibility with Open Database Connectivity (ODBC) data sources that you can access by using the Jet database engine.

New SQL commands and syntax

The SQL commands, syntax, and query processor used by the Microsoft Jet 4.0 database engine have been enhanced to support Unicode, and to conform more closely to the ANSI SQL-92 specification. Because Microsoft SQL Server 7.0 and SQL Server 6.5 also support Unicode and much of the ANSI SQL-92 specification, these enhancements make it easy to write SQL statements that are compatible with both native Access databases (MDB files) and Microsoft SQL Server databases. The enhancements also simplify converting Visual Basic for Applications code that contains SQL statements to Microsoft SQL Server.

Important   The new Microsoft Jet 4.0 SQL commands and syntax are available only from VBA code written by using the ADO programming model.

Connection control

The Jet database engine provides a new connection control feature that allows you to deny access to all new user connections and current users after they close the database. This connection control is useful for an administrator who needs exclusive access to a database to perform maintenance tasks such as compacting the database or making design changes to the database.

User list

The user list feature allows you to view a table of user information in a multiuser database. You can use Visual Basic for Applications code and the ADO OpenSchema method to obtain the user list. The user list returns the following information for each user:

• Network name of the user’s computer.
• The user’s security ID when the user is connected.
• Whether a connection was terminated normally after a user disconnects.

Record-level locking

With the increased page size required to support Unicode, performance might decrease, and concurrency might diminish, which means that the same sets of data and objects might not be available to multiple users. However, the record-level locking feature in the Jet database engine minimizes the impact of the increased page size, and increases performance and concurrency. Instead of locking an entire data page or multiple records, an application can be programmed to lock only a single record at a time.

Lock promotion

When an SQL statement is carried out, or when a transaction that modifies a large number of records in a table is carried out, the Jet database engine places individual write locks on all corresponding index and data pages in the database. The individual write lock feature maximizes concurrency, but it might significantly decrease performance because the locks need to be set and maintained. This is particularly true when the database is on a server, and the database is being accessed over a local area network (LAN).

The new table lock feature permits a user to open a table exclusively and then to modify records in the table without locks being placed on either the corresponding index pages or data pages. This reduces concurrency because only one user can update the table, but it increases performance when large numbers of records are being modified.

The Jet database engine provides an option to automatically attempt to promote the page locks on a table to an exclusive table lock when large numbers of page locks are being placed on a table. This option is controlled by the value entry PagesLockedToTableLock, which is located in the HKEY_Local_Machine\Software\Microsoft\Jet\4.0\Engines\Jet 4.0 subkey in the Windows registry. The default value is 0, which disables the capability. A value greater than 0 specifies the page lock count at which promotion to an exclusive table lock should be attempted.

For example, if PagesLockedToTableLock is set to a value of 50, then on the fifty-first data page lock, Microsoft Jet tries to escalate the user’s shared table read lock to an exclusive table read lock. If the promotion is unsuccessful, the Jet database engine retries on the 101st data page lock and so on. The lock count is maintained on a per-table basis, and it is reset when the transaction level reaches 0 (zero).

New replication features

Database replication is the process of sharing data or database design changes between copies of an Access database in different locations without having to redistribute copies of the entire database. Replication involves producing one or more copies, (replicas) of a single original database (the Design Master). Together, the Design Master and its replicas are called a replica set. By performing a process called synchronization, changes to objects and data are distributed to all members of the replica set. You can make changes to the design of objects only in the Design Master, but you can make changes to data from any member of the replica set.

Note   Microsoft SQL Server also provides replication features that use a different publish-and subscribe-model.

In previous versions of Microsoft Jet, Microsoft SQL Server data is replicated to an Access database; but changes to the Access database cannot be used to update the Microsoft SQL Server database because replication is unidirectional — that is, replication goes from a SQL Server publisher to an Access subscriber. However, Microsoft Jet 4.0, and Microsoft SQL Server 7.0 support bidirectional replication between Access and Microsoft SQL Server databases.

The bidirectional replication feature ensures that changes to data in a Microsoft SQL Server database can be replicated to an Access database and that changes to the data in Access can be synchronized to and reconciled with the SQL Server database.

In previous versions of Microsoft Jet, replication differentiates between synchronization conflicts and synchronization errors. Synchronization conflicts occur when two users update the same record in two different databases in a replica set. Synchronizing the two databases succeeds when one of the two sets of changes is applied to both databases. Thus, only one set of changes is made. Synchronization errors occur when a change to data in one database in a replica set cannot be applied to another database in the replica set because the change violates a constraint such as referential integrity or uniqueness.

In Microsoft Jet 4.0, replication events that cause synchronization conflicts and synchronization errors are identified as synchronization conflicts, and a single mechanism is used to record and resolve the conflicts, making conflict resolution easy. When a conflict occurs, an acceptable change is selected and applied, and the conflicting change is recorded as a conflict in all replicas. You can use the new Conflict Resolution Wizard to reconcile and resolve synchronization conflicts either with Microsoft SQL Server 7.0 or with Microsoft Jet 4.0 replicated databases.

In Microsoft Jet 3.5, conflicts are determined at the record level. In other words, when two users in two different replicas change the same customer record, but each user changes a different field in the record, the two records conflict when the replicas are synchronized because conflicts are determined at the record level.

In Microsoft Jet 4.0, field-level conflict resolution means that changes to the same record in two different replicas causes a synchronization conflict only when the same field in the same record is changed. Field-level change tracking and conflict resolution significantly do the following:

• Reduce the potential for conflicts.
• Simplify the maintenance of replicated databases.

Top

Microsoft Data Engine

Microsoft Data Engine (MSDE) is a new technology that provides local data storage compatible with Microsoft SQL Server 7.0. You can use MSDE as a small workgroup database server, and because Access now provides the Access project file (ADP file) to connect to SQL Server databases, you can use MSDE as an alternative to the Microsoft Jet database engine. MSDE runs on Windows NT Workstation 4.0, Windows 2000, and Windows 95 or later. It is designed and optimized for use on smaller computer systems such as a single-user computer or small workgroup server.

MSDE does not limit the number of users who can connect to its database, but MSDE is optimized for five users. For a larger number of users, use Microsoft SQL Server 7.0. Databases created with MSDE are fully compatible with SQL Server 7.0, and they support many of the features of SQL Server 7.0, including most Transact-SQL commands.

MSDE also logs transactions, which means that if anything goes wrong while writing to an MSDE database, such as a disk error, network failure, or power failure, then MSDE recovers from its transaction log and reverts to its last consistent state. This gives MSDE databases better reliability than Microsoft Jet databases (MDB files), which do not log transactions.

Because MSDE is based on the same database engine as Microsoft SQL Server, most Access projects can run unchanged on either version. However, MSDE has a 2-gigabyte database size limit and supports up to two processors for Symmetrical Multiprocessing (SMP). In a replicated database environment, MSDE can act as a replication subscriber for both transactional and merge replication and as a replication publisher for merge replication. However, unlike SQL Server, MSDE cannot be a replication publisher for transactional replication.

Compared with using Access with a Microsoft Jet database (MDB file), using MSDE does require more memory. The minimum supported configuration for running MSDE is a computer with a Pentium 166 processor and 32 megabytes (MB) of RAM. MSDE does manage its memory usage dynamically; so MSDE reacts to operating system pressure on memory resources to allocate as much memory as it can effectively use, but it stops allocating memory and even, if needed, gives back memory to ensure that other applications have memory available. However, if your solution requires the minimum usage of memory resources, use Access with a Microsoft Jet database.

You can install MSDE from Microsoft Office 2000 Disc 1 by double-clicking Setupsql.exe in the SQL\x86\Setup folder.

Top

See also

For more information about the Microsoft Jet database engine, see Microsoft Jet Database Engine Programmer’s Guide, Second Edition.

For more information about replicating data between Microsoft SQL Server and Microsoft Access, see SQL Server Books Online, which is installed with SQL Server 7.0.