Description of the new features that are included in Microsoft Jet 4.0

This article describes the new features of Microsoft Jet 4.0, and the article also compares some of the changes between Microsoft Jet 3.5 and Jet 4.0.

Storing characters in Unicode

One of the key features now available in Microsoft Jet, version 4.0 is it capability to provide Unicode support. This feature brings Jet closer to the data storage format used by Microsoft SQL Server. All character data-- that is, data stored in fields defined as the Microsoft Jet data types Text and Memo, which are equivalent to the Microsoft SQL Server data types Char, Varchar, and so on--is now stored in the Unicode two-byte character representation format. It replaces the Double-Byte Character String (DBCS) format used in earlier versions of the Microsoft Jet database engine for storing character data for certain languages, such as Japanese, Chinese, and others.

To accommodate the change to Unicode data and enable all existing data to be successfully converted, the internal unit of storage (the page size) was increased from 2 kilobytes (KB) (2,2048 bytes) to 4 KB (4,096 bytes). This enabled an increase in the maximum database size for Microsoft Jet databases, allowing them to go from a maximum of 1.07 gigabytes (GB) in earlier versions of Microsoft Jet, to 2.14 GB for Microsoft Jet 4.0.

While the Unicode representation of character data requires more space to store each character (two bytes instead of just one byte), fields with string data types can be defined to automatically compress the data, if possible. Therefore, with most character sets, character data can be stored in a compressed format, such that the use of the Unicode representation improves reducing the database size. There is some impact, however, based on whether character data is stored in fields defined as Text or Memo. Jet Memo fields have a limitation with respect to compression (how to enable Unicode compression, when it is enabled by default, and where it is applied is described later in this article). Note that non-character data types, such as Integer, Currency, Date/Time, and so on are unaffected by the implementation of Unicode.

NT-compatible sorting

Implementing the Unicode representation for the storage of character data enabled the Microsoft Jet Database Engine to develop and use a sorting mechanism that is based on the native Microsoft Windows NT sorting functionality. This sorting mechanism uses Windows NT's Locale Identifiers (LCID) and can support all sort orders supported by Microsoft Windows NT.

This sorting mechanism is also used by Microsoft Jet when running on Microsoft Windows 95, providing a copy of Unicode functionality that is available in Windows NT yet omitted from Windows 95. This makes it possible to properly sort on Windows 95 those languages that are available on Windows NT, instead of just the system default language that Windows 95 supports within its ANSI sorting. This sorting mechanism is also used by Microsoft Jet when running on Microsoft Windows NT. Therefore, there is consistency in sorting across operating systems and absolute consistency with native Windows NT sort orders.

This sorting mechanism is also used by Microsoft SQL Server 7.0 and 2000, and by Visual Basic 6.0, providing greater cross-product consistency.

High performance in sorting is essential. Before the Windows NT sorting mechanism was added to Microsoft Jet 4.0, it was substantially optimized for high performance. In most cases, the Microsoft Jet sorting mechanism is 50% faster than the Windows NT equivalent, and for some languages such as Thai, the speed improvement is even greater than 50%.

Overview of Jet 4.0 data types

To make it easier to upsize Microsoft Jet databases to Microsoft SQL Server databases, and for better compatibility between Microsoft Jet and Microsoft SQL Server, and for the sake of replication, Microsoft Jet data types have been aligned with those of Microsoft SQL Server to a greater degree. In some instances, this has also provided for greater compatibility with ODBC data sources that can be accessed via Microsoft Jet.

Character (that is, Text) data types still have a maximum length of 255 characters. Unicode support now makes this maximum length viable for all languages. Supported synonyms for the Text data type is Char, Varchar, Character Varying, Nchar, National Character, National Char, Nvarchar, National Character Varying, and National Char Varying.

The use of the Text keyword without an accompanying length specification has been changed. It is now a synonym for Memo. This better aligns the use of the Microsoft Jet Text keyword with its use in Microsoft SQL Server. You can continue to use the Text keyword with a length specification, for example Text (100) to define a fixed-length character data field.

Examples to illustrate the use of the Text data type keyword is equivalent to and aligns with the SQL Server definition and syntax for Text. This next example: is equivalent to and will provide backward compatibility for most existing Microsoft Jet applications.

LongText (that is, Memo) allows for a maximum length of approximately 2.14 GB. With the implementation of Unicode, this would be approximately 1.07 GB characters. Supported synonyms are Text, Ntext, Memo, LongChar, and Note.

Binary(n) data types allow for a maximum length of 255 characters. Supported synonyms are Varbinary, Binary Varying, and Bit Varying.

LongBinary data types will now allow for a maximum length of approximately 2.14 GB. Supported synonyms are Image, General, and OLEObject.

Date/Time data types are unchanged from earlier versions of Jet. The synonym Timestamp is no longer supported because the corresponding SQL Server Timestamp does not correlate directly to the Date/Time data type.

Single data types are unchanged from earlier versions as well. Supported synonyms are Real, Float4, and IEEESingle.

Double data types are also unchanged and the supported synonyms are Float, Double Precision, Float8, and IEEEDouble. The synonym Numeric is no longer supported for the Double data type. The keyword Numeric is now used to define a field as an exact numeric data type corresponding to the SQL Server Decimal or Numeric data types.

Byte is unchanged and the synonym is Tinyint.

Integer is unchanged and the supported synonyms are Smallint, Integer2, and Short.

LongInteger is unchanged and the supported synonyms are Int, Integer, and Counter. The synonym AutoIncrement is no longer supported (see the information later in this article for on Auto-Increment fields).

Currency data types are unchanged and the synonym is Money. Data accessed via ODBC that are typed as Sql_Decimal or Sql_Numeric (that is, SQL Server Decimal or Numeric fields) are no longer mapped to Microsoft Jet Currency types.

Boolean types are unchanged and the supported synonyms are Bit, Logical, and Yes/No.

GUID (Globally Unique Identifiers) are unchanged and the supported synonym is UniqueIdentifier.

Auto-Increment fields can only use the LongInteger data type for defining fields whose values are automatically generated by the Microsoft Jet Database Engine. The following shows an example of how to define an auto-incrementing field: The field FieldName1 is based on the LongInteger data type, the value of FieldName1 for the first record inserted into TableName will be 10, and the value in each subsequent record will be incremented by 5.

The keyword Counter can be used instead of the keyword Identity. Both the seed (the starting value) and the incremental value are optional. If not specified, both default to a value of 1.

Both the seed and the increment can be modified using an ALTER TABLE statement. New records inserted into the table will have values that are automatically generated for the field based on the new seed and increment values. If the new seed and increment can yield new values that match existing values generated by the preceding seed and increment, duplicates will be created. If the field is a Primary Key, then inserting new records may result in errors when duplicate primary keys are created. The following is an example of using the ALTER TABLE statement to set new seed and increment values for an auto-increment field:

A new Microsoft Jet 4.0 data type

Decimal data types are new to Microsoft Jet 4.0. It is an exact numeric data type that holds values from -10^28 - 1 through 10^28 - 1. You can define both precision (1 - 28) and scale (0 - defined precision). The default precision and scale are 18 and 0, respectively. Supported synonyms are Dec and Numeric. Data accessed via ODBC that is typed as Sql_Decimal or Sql_Numeric will now be mapped to the Microsoft Jet Decimal, instead of Currency. This data type is not supported via Data Access Objects (DAO). It is supported only with ActiveX Data Objects (ADO).

Searching on character and memo data types

In earlier versions of Microsoft Jet, Memo fields could not be indexed. Now, indexes on Memo fields are supported. The first 255 characters of data in a Memo field are used to construct the index. When wildcard searches are performed, only the index is used. This provides good performance, but limits the search to just the first 255 characters of data. The reason that this limited indexing and searching capability was added was to provide faster sorting of the Microsoft Access Hyperlink data type, which is based on the Microsoft Jet Memo data type.

Compressible data types

All string data type fields can be defined to store data in a compressed format. If you have defined a string data type field with this attribute, data will be compressed as it is stored, and uncompressed when retrieved from the field.

This attribute was added for Character fields because of the change to the Unicode character representation format. Unicode characters uniformly require two bytes for each character. For existing Microsoft Jet databases that predominantly contain character data, this could mean that the database file would nearly double in size when converted to the Microsoft Jet 4.0 format. Yet the Unicode representation of many character sets (those formerly denoted as Single-Byte Character Sets, SBCS) can easily be compressed to a single byte. The following is an example of how to define a field that is to be compressed: Memo fields can also be defined to store data in a compressed format. There is a limitation, however. Only Memo field values that, when compressed, will fit within 4,096 bytes or fewer, will be compressed. All other Memo field values will remain uncompressed. This means that within a particular table, for a particular Memo field, some of the data may be compressed, and some may not.

It should be noted that when going through the Access User Interface (UI), Access will always add the Unicode compression attribute whenever applicable. The only time that the end-user needs to be concerned about adding the compression attribute is when creating a table with the CREATE TABLE SQL syntax. The compression attribute is not accessible via DAO when going through the object model to create a string data type.

SQL extensibility

There were many enhancements that were added to the Microsoft Jet 4.0 SQL implementation to support new functionality and to make it conform more closely to the ANSI SQL 92 specification. Many of these enhancements also make it easier to write SQL statements that will inter-operate between Microsoft Jet and Microsoft SQL Server.

But to maintain backward compatibility with earlier versions of Microsoft Jet, the enhancements to SQL are available only when the database engine is set to operate in ANSI SQL 92 mode. Queries created using ANSI SQL 92 mode are flagged as such when stored in the database. This enables Microsoft Jet to handle databases that contain a mixture of non-enhanced queries and those created using the enhanced syntax.

Security

Support for definition of database security via SQL has been added. Instead of using either DAO or ADO for things such as adding users and groups, setting and unsetting privileges on database objects, administering passwords, and so on, you can use the following SQL syntax: The following are a series of examples that illustrate the syntax for creating users and groups, adding users to groups, creating and modifying a database password, and so on. A brief explanation is provided for each example.

To create users or groups (one or more users or groups can be created at one time): To add a user to an existing group: To drop a group, or to drop user(s), or to drop users from a group: To create or modify a database password: To modify a user's password: Grant/Revoke syntax can be used for setting and modifying security on database objects. Note that the granting user must have sufficient privileges to be able to grant permissions to others. The objects for which security can be defined are:

• Container
• Index
• Query
• Table

The basic form of the Grant/Revoke syntax is shown in the following examples: More than one privilege may be granted or revoked at one time for more than one user or group: Privileges may be granted or revoked on database objects other than tables. A list of the types of privileges that can be granted or revoked immediately follow this next example: The types of privileges specified by ANSI that can be granted or revoked are as follows:

• SELECT
• DELETE
• INSERT
• UPDATE

The type of privileges specific to Microsoft Jet that can be granted or revoked are as follows:

• ALL PRIVILEGES
• DROP
• SELECTSECURITY
• UPDATESECURITY
• DBPASSWORD
• UPDATEIDENTITY
• CREATE
• SELECTSCHEMA
• SCHEMA
• UPDATEOWNER

The UPDATEIDENTITY privilege allows a user to change the values in auto-increment columns. Typically, this privilege is reserved for the database engine itself.

There are also a few privileges that are specific to the database itself. They are:

• CREATEDB
• EXCLUSIVECONNECT
• CONNECT

In general, the privileges that are defined above parallel those found in the DAO programming model.

Views

Support for the definition of Views has been added to Microsoft Jet. This capability is built on top of Microsoft Jet's stored query functionality and offers almost exactly the same capability. The View syntax, however, when written within the guidelines of ANSI View semantics, can be migrated to other SQL databases, where as Microsoft Jet queries cannot. The following provides some examples of view definitions and some of the basic rules: The query underlying the View definition cannot contain SELECT INTO, nor can there be any parameters. A View name cannot be the same as any existing table name. For the View to be updateable, the underlying query must be updateable. If any two columns in the tables referenced by the underlying query have the same name, then the View must define its own unique column names.

To delete an existing view, use the DROP VIEW syntax. For example: This syntax can actually be used to drop any stored query within a Microsoft Jet database, even if the query were not originally defined using the CREATE VIEW syntax.

Procedures

Support for the definition of Procedures has been added to Microsoft Jet. This capability is built on top of Microsoft Jet's stored query functionality of action queries (queries based on UPDATE, DELETE, SELECT INTO, and DROP statements) and SELECT queries that contain parameters. The following are some examples of Procedure definitions: Note that procedure names cannot be the same as an existing table name.

To delete a procedure, simply reference the procedure name in the DROP PROCEDURE statement. For example: Note that although Microsoft Jet procedures are based on stored queries and DROP PROCEDURE can be used to delete any action query, it cannot be used to delete Views that are also based on stored queries.

To execute an existing procedure, use the EXECUTE verb, followed by the procedure name: If there are parameters defined in the procedure, parameter values follow the procedure name as a comma separated-list enclosed in parentheses:

Transactions

Microsoft Jet SQL now supports invocation and termination (committing or rolling back) of transactions. Note that while ANSI SQL specifies that a new transaction is started automatically following a COMMIT or ROLLBACK, Microsoft Jet does not follow this model. Thus, an additional transaction verb is defined to explicitly start transactions, because Microsoft Jet does not automatically start transactions. To explicitly start a transaction use: To end a transaction and commit the work done within the transaction (statements executed since the transaction began), use: To end a transaction and rollback statements executed since the transaction began, use:

Tables

The Microsoft Jet CREATE TABLE syntax has been enhanced in several ways. One new feature added to the Jet CREATE TABLE syntax is Check Constraints. This new SQL grammar allows the user to specify business rules that can span more than one table. The proper syntax for a constraint is as follows: The above example creates the CHECK CONSTRAINT after the column definition. While ANSI allows this to occur as part of the column definition, this approach is not supported when using Microsoft Jet. Instead, use:

Referential integrity

Declarative referential integrity has been extended to include the definition of cascading deletes and updates. For example, consider the following table definition for a table called Customers: Now consider the following definition of a table called Orders, which defines a foreign key relationship referencing the primary key of the Customers table: Notice that both an ON UPDATE CASCADE clause and an ON DELETE CASCADE clause are defined on the foreign key. The ON UPDATE CASCADE means that if a Customer's Identifier (CustomerID) is updated in the Customers table, the update will be automatically passed to the Orders table, where each order containing the same CustomerID value will be updated automatically with the new value. The ON DELETE CASCADE means that if a customer is deleted from the Customers table, all rows in the Orders table containing the same CustomerID will be deleted as well.

Declarative referential integrity has also been extended to include the definition of cascading NULL values to foreign keys. Assuming the same Customers table definition as in the example above, consider the following definition of the Orders table: Notice that both an ON UPDATE SET NULL clause and an ON DELETE SET NULL clause are defined on the foreign key. The ON UPDATE SET NULL means that if CustomerID is updated in the Customers table, the corresponding foreign key values in the Orders table will automatically be set to NULL. Similarly, the ON DELETE SET NULL means that if a customer is deleted from the Customers table, all corresponding foreign keys in the Orders table will automatically be set to NULL

Fast foreign keys

Typically, when a foreign key is defined, an index based on the fields that make up the foreign key are created automatically. In many instances, this enhances performance when executing operations that maintain referential integrity.

However, foreign key indexes can also reduce performance and concurrency. In cases where the values in a foreign key index are highly duplicated, using an index can be less efficient than simply scanning the table. And maintaining such an index as records are inserted and deleted from the table, can degrade performance even further.

Also, the locking of index pages decreases concurrency. In other words, this can increase the likelihood that a user will have to wait until a lock is released by another user before their query can continue execution.

Finally, modifying index pages requires that lock requests be placed over the network. This additional I/O can further degrade performance.

Modifying existing tables

The ALTER TABLE syntax has been extended to include the action ALTER COLUMN. Without direct support for altering a field in earlier versions of Microsoft Jet, the only way to change a field's definition was to add a new field, copy the data from the existing field, then drop the original field. Support for ALTER COLUMN simplifies changing field definitions. For example, given the following table definition: The data type of the field FieldName1 can be changed using the ALTER COLUMN syntax as follows: Additional syntax for the ALTER TABLE: As a side note (and in addition) to changing field data types or changing the seed and increment value of an Auto-increment data type, the programmer can also drop fields and constraints.

Access to external data

Microsoft Jet provides the capability to read and write, import and export, tabular data in other popular formats, such as dBASE, Excel, Lotus, Paradox, and others. The technology that supports this capability is referred to as the Microsoft Jet Installable ISAMs (IISAMs). The following provides a brief review of significant changes to each of the format specific IISAMs.

Paradox and dBASE

Read/write access to Paradox and dBASE data will be supported through the Borland Database Engine (BDE). Users who need to be able to update, as well as read Paradox and dBASE data, and users who need access to the most recent versions of Paradox and dBASE data, will need to obtain the BDE from a supplier other than Microsoft.

Users who do not have the BDE will still be able to import and export older versions of Paradox (version 5.0 and earlier) and dBASE (version 5.0 and earlier) data via the Microsoft Jet proprietary technology that has been shipped with earlier versions of Microsoft Access and the Microsoft Jet database engine.

For additional information about using Paradox data and dBASE data, click the following article numbers to view the articles in the Microsoft Knowledge Base:

FoxPro

Access to Microsoft FoxPro data will be supported only through the Microsoft FoxPro ODBC driver. Access to FoxPro data via the Microsoft Jet proprietary technology that has been shipped with earlier versions of Microsoft Access and the Microsoft Jet database engine is no longer supported.

For additional information about using FoxPro data, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Exchange

Support has been added to the Exchange IISAM to allow it to access the Windows Address Book (WAB) used by the Outlook Express mail client. Enhanced column name support as been essentially added to the IISAM. The Exchange IISAM was previously only able to retrieve data from Exchange client columns. It has been extended to retrieve data from all Outlook columns, including Outlook user-defined columns.

The Exchange IISAM is now able to also use indexes maintained by the Exchange Server to locate and retrieve data. This provides a significant boost in performance when searching for data that meets specific criteria.

Excel

The Excel IISAM has been updated to support the Excel 2000 format. The Excel 3, 4, 5, 95, and 97 formats will continue to be supported by the Excel IISAM.

Text/HTML

The Text/HTML IISAM is now able to handle HTML pages written in the Unicode representation format. The IISAM will automatically detect the Unicode format and the character set using the same technology that is used by Microsoft Internet Explorer and Microsoft Office products.

Lotus

No significant functional changes were made to the Lotus IISAM.

For additional information about accessing external data, click the following article numbers to view the articles in the Microsoft Knowledge Base:

Connection control

The connection control (also known as passive shutdown) feature prevents users from connecting to a database. This capability is useful for a database administrator who needs to acquire exclusive access to a database to perform maintenance; for example, compacting the database or when making updates to the database schema.

When connection control is invoked, users currently connected to a database will remain unaffected until they disconnect. Once disconnected, they will be unable to reconnect until connection control is revoked. The following scenarios provide additional insight into how this capability works:

• Five users are in a database. User Five initiates passive shutdown. User Six tries to connect to the database, but is denied access, and an error message is returned stating that user Five is preventing the database from being opened.
• Five users are in a database. User Five initiates passive shutdown. User One closes the database and tries to reconnect to the database, but is denied access, and an error message is returned stating that user Five is preventing the database from being opened.
• Five users are in a database. User Five initiates passive shutdown. User Five closes the database. User Six tries to open the database and is successful. This is because passive shutdown only persists while the user that called it remains connected to the database.
• Five users are in a database. User Five initiates passive shutdown. Users One through Four exits the database. User Five calls the user list functionality and determines that no other users are in the database. User Five closes the database, which immediately allows all other users to reconnect.

For information on how to invoke connection control, refer to your ADO user documentation.

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

User list

The user list feature provides a way of finding out who is currently connected to a Microsoft Jet database. The list can be obtained via the ADO programming interface. Refer to ADO user documentation for information on how to return a user list, or see the Knowledge Base articles listed directly above. The user list returns the following information on each user:

• Computer name of the user.
• Security name (that is, User ID).
• Whether or not the user is currently connected to the database (a user's ID remains in the lock database until the last user disconnects or until the slot is reclaimed for a new user connection).
• Whether or not the user connection was terminated normally.

The user list capability can be used in conjunction with the connection control capability to determine which users are still connected, so that they can be asked to disconnect.

The user list capability is also useful in isolating problems with database corruption that is associated with the activities of a specific user.

Lock promotion

When a SQL statement is executed or when a transaction is executed that modifies a large number of records in a table, Write locks will be placed on all corresponding index and pages in the database. Although the placing of discrete locks maximizes concurrency, it can significantly decrease performance because of the overhead involved in setting and maintaining the locks. This is particularly true when the database is on a server and is being accessed over a local area network.

Capability has been added that permits a user to open a table exclusively and modify records in a table without locks being placed on either corresponding index or pages. This reduces concurrency (only one user is being permitted to update the table), but will increase the performance where large numbers of records are being modified. As an option to requiring a user to programmatically request exclusive access to a table for performing updates, Microsoft Jet will, when large numbers of page locks are being placed on a table, attempt to promote the page locks to an exclusive table lock. Whether or not this capability is turned on is controlled by a registry entry, PagesLockedToTableLock. The default value for the registry entry 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 the PagesLockedToTableLock entry is set to a value of 50, then on the 51st page lock, Microsoft Jet will try to promote the user's shared table read locks to an exclusive table lock. If the attempted promotion is unsuccessful, it will retry on the 101st page lock, and so on.

Record-level locking

With the increased page size (from 2K to 4K) required to support the Unicode format representation, there is the potential for decreased performance and concurrency. To minimize the impact of the increased page size and respond to a long-standing request from developers building applications based on the Microsoft Jet database engine, row-level locking was added to Jet 4.0.

Instead of locking an entire page and possibly multiple rows of data, an application can choose to lock only a single record at a time, thereby increasing concurrency and performance.

A database can be opened in one of two modes:

• Page Locking Mode
• Record/Page Locking Mode

Page Locking Mode is essentially the former method of locking pages, that is, locking the entire 4 KB page whenever a user updates a value in a record.

The new Record/Page Locking Mode, as its name implies, supports either record or page locks. Note that while record-level locking reduces concurrency conflicts and thereby increases performance, the disadvantage of record-level locking is also performance related. Performance decreases when there are many records being updated at once (for example, a SQL Data Manipulation Language (DML) statement or a loop in a program that modifies a large number of records). This is because a lock request must be placed for each record, instead of one lock request for each page. Therefore, depending on the size of a record, performance could be severely hampered. This is the reason why both locking modes are available in Jet 4.0, giving the developer the option of returning to the former Page Locking Mode if that method is desired. By default, Access will enable record-level locking, but the user can disable this within Microsoft Access by pointing to Options on the Tools menu, clicking the Advanced tab, and clicking to clear the Open databases using record-level locking check box. By default, access to data via Microsoft Access forms will use record-level locking (in other words, two users can update or delete different records on the same page).

A limitation to record-level locking is that users will not be notified when another user is locking the record. Also, record-level locking is not enabled for Memo data types.

When you use SQL DML bulk-operation statements, Jet defaults to page-level locking. When you use Access forms, row-level locking is enforced if you have selected the Record-Level Locking option under Options on the Tools menu. The same is true if you gain access to the Jet database by using DAO while you are in the Microsoft Access session. For additional information about how to enforce row-level locking in this scenario from a Microsoft Access session that is not enforcing record-level locking, or while you are using DAO outside of a Microsoft Access session, click the following article number to view the article in the Microsoft Knowledge Base:

Replication

Bi-directional replication with Microsoft SQL Server

With earlier versions of Microsoft Jet, Microsoft SQL Server data could be replicated to a Microsoft Jet database, but changes made in the Microsoft Jet database could not be used to update the Microsoft SQL Server database. Replication was uni-directional, going from a SQL Server publisher to a Jet subscriber.

With version 4.0 of the Microsoft Jet database engine and version 7.0 and 2000 of Microsoft SQL Server, support for bi-directional replication between Microsoft Jet and Microsoft SQL Server has been added. Not only can changes made in a Microsoft SQL Server database be replicated to a Microsoft Jet database, but changes made to the data in Jet can be synchronized to and reconciled with the SQL Server database. There are some limitations however:

• Only data may be replicated between Microsoft Jet and Microsoft SQL Server. Microsoft Access application objects (i.e. forms, reports, macros, and modules) cannot be replicated to Microsoft SQL Server and will continue to reside only in a Microsoft Jet database.
• The only topology supported in the Microsoft Jet/Microsoft SQL Server replication is the "Hub and Spoke". Microsoft SQL Server is always the Hub. The Microsoft Jet replicas at the Spokes cannot synchronize with other Microsoft Jet replicas. They can only synchronize with the Hub (Microsoft SQL Server).

Detailed documentation on setting up and running Microsoft Jet/Microsoft SQL Server replication is provided in the Microsoft SQL Server books online and its Readme file.

Resolving replication conflicts

In earlier versions, Microsoft Jet Replication differentiated between synchronization conflicts, and synchronization errors. Synchronization conflicts occurred when two users updated the same record in two different databases within a replica set. Synchronizing the two databases would succeed, but only one of the two sets of changes would be applied to both databases. Therefore, one user would lose his/her changes. Synchronization errors occurred when a change to data in one database within the replica set could not be applied to another database within the same replica set, because it would violate some constraint, such as referential integrity or uniqueness.

With Microsoft Jet 4.0 Replication, the events that cause synchronization conflicts and synchronization errors are both viewed simply as synchronization conflicts, and a single mechanism is used to record and resolve them, making resolution of such problems easier. Whenever a conflict occurs, a winning change will be selected and applied, and the losing change will be recorded as a conflict at all replicas. The new Conflict Resolution Wizard can then be used to reconcile and resolve synchronization conflicts. Note that the same Conflict Resolution Wizard can be used with either SQL Server 7.0 and 2000, or Microsoft Jet 4.0 replicable databases. The following summarizes the types of synchronization conflicts that can be encountered:

• Simultaneous Update Conflicts.
• Unique key Violation Conflicts.
• Table-Level Validation Violation Conflicts.
• Referential Integrity Violation Conflicts.
• On Delete.
• On Update.


• Foreign Key Violations.
• Locking Conflicts.

Simultaneous Update Conflicts
This conflict occurs when another replica updated the same record and your copy of the record lost the conflict. The solution is to either resubmit your update or delete the conflict record, accepting the other record's changes.

Unique key Violation Conflicts
The conflict is that your record has the same key value as another record, when only unique values are permitted. The solution is to either change the key value in the conflict record (or the winning record) and resubmit your record, or delete the conflict record, accepting the other record's changes.

Table-Level Validation Violation Conflicts
The conflict occurs when a record contains a field value that does not meet a table-level validation constraint. The solution is to either update the field value that is violating the validation rule and resubmit the conflict record, or to delete the conflict record.

Referential Integrity Violation Conflicts One of the reasons why this conflict can occur is because the primary key record has been deleted by another replica and therefore, the foreign record has been rejected. The solution is to either create a new primary key record that satisfies the referential integrity constraint and resubmit your update, or delete the conflict record completely. Another reason why this conflict can occur is because the primary key record has been updated by another replica and therefore, the foreign record has been rejected. The solution is to either create a new primary key record that satisfies the referential integrity constraint, or modify the foreign key value in the conflict record to match a valid primary key value and resubmit your update, or delete the conflict record completely.

Foreign Key Violation
A conflict can occur when a foreign key violation results from an invalid primary key record that was involved in a replication conflict. The solution is to either create a new primary key record that satisfies the referential integrity constraint, or modify the foreign key value in the conflict record to match a valid primary key value, and then resubmit the foreign key record, or delete the conflict record completely.

Locking Conflicts
A conflict can occur when a record change cannot be applied during synchronization because the table is locked by another user. The solution is to resubmit the conflict record once the other user is out of the table.

Priority-based conflict resolution

In version 3.5 of the Microsoft Jet database engine, synchronization conflicts were resolved based upon a simple algorithm whereby the most often changed copy of a record won. In the case of both copies having been changed the same number of times, this algorithm was deterministic, yet unsophisticated.

Microsoft Jet 4.0 introduces an algorithm whereby replicas in a replica set can be assigned priorities and the replica with the highest priority wins in the case of a synchronization conflict. Where priorities are equal, the replica with the lowest ReplicaID wins.

The priority based conflict resolution algorithm is consistent with the algorithm implemented in Microsoft SQL Server 7.0 replication. Replicas can be assigned a priority (a value between 0 and 100) inclusive. A replica is assigned a default priority that is 90% (0.9) of its parent.

Column-level conflict resolution

In Microsoft Jet 3.5, conflicts were determined at the record level. In other words, if two users in two different replicas changed a customer record for the same customer, but each changed a different field within the record, the two records would still conflict when the replicas were synchronized. For example, if one user changed the zip code, and the other user changed the phone number, although the changes themselves do not conflict (since the changes involved two different fields), a synchronization conflict would still occur, as conflicts were determined at the record level.

Microsoft Jet 4.0 implements field-level conflict resolution, whereby changes to the same record in two different replicas will cause a synchronization conflict only if the same column or field is changed. Therefore, in the above scenario, there would no longer be a synchronization conflict because the two users changed the values of different fields. Field-level tracking of changes and conflict resolution will significantly reduce the potential for conflicts and simplify the maintenance of replicated databases.

Microsoft Jet field-level change tracking and conflict resolution will work in conjunction with the corresponding Microsoft SQL Server 7.0 capability when Microsoft Jet/Microsoft SQL Server replication is used.

Field-level conflict resolution is the default when a database is made replicable. To specify record-level conflict resolution for a table, it must be set prior to making the table replicable.

For additional information about Jet 4.0 replication, click the following article number to view the article in the Microsoft Knowledge Base:

New Access project storage format

In Microsoft Jet 3.5 replication, individual Microsoft Access objects (such as forms, reports, modules, and so on) can be identified and tracked, allowing changes to individual objects to be synchronized. In other words, if a Microsoft Access form is changed in the Design Master replica and no other objects are change, only the changes to the form are replicated when the replica set is synchronized.

However, in Microsoft Access 2000, all Microsoft Access objects (such as forms, reports, modules, and so on) are stored in a single binary large object (BLOB) within the database file or in a separate project (*.adp) file. In this format, the individual objects cannot be identified or tracked by Microsoft Jet replication. What this means is that if the Microsoft Access project in the Design Master is made replicable and any single object is modified, the entire project is replicated when a replica set is synchronized. However, you can choose to not make the Microsoft Access project replicable when you create the Design Master. In this case, the Microsoft Access project in each of the replicas is not replicable, and all objects created in a replica are local.

Replica visibility

Microsoft Jet 4.0 replication defines three degrees of visibility for replicas. A replica's visibility can be defined as:

• Global
• Local
• Anonymous

Local and Anonymous replicas provide a way of controlling topology. Anonymous replicas are important for Internet-based replication where you do not want to keep track of every time the database is downloaded over the Web. The following provides more detailed information about each of the different degrees of replica visibility.

Global
A Global replica is a replica that can synchronize with all other global replicas in a replica set. A global replica can also synchronize with any replica it created, with some exceptions (the description of Local and Anonymous replicas that follow will enumerate the exceptions). When a Jet database is made replicable, its visibility is set to Global. Users of Microsoft Jet 3.5 replication are familiar with the characteristics of Global replicas because all replicas created using Microsoft Jet 3.5 are Global replicas.

Local
A Local replica can synchronize only with its parent replica, which is a Global replica, and cannot synchronize with other replicas in the replica set. Local replicas permit finer control of the topology of a replica-set. For example, they can be used to enforce a star topology at individual sites where you want to ensure that synchronization between the sites goes through a Global hub at each site.

Anonymous
An Anonymous replica, like a Local replica, can synchronize only with its parent, a Global replica. The purpose of having Anonymous replicas is to permit there to be large numbers of replicas that participate in a replica set, to reduce the amount of information stored about a replica set, and to reduce processing overhead. This is consistent with supporting replica sets whose subscribers are distributed across the Internet. Unlike a Local replica, a Global replica cannot schedule synchronization with an Anonymous replica. An Anonymous replica initiates synchronization with its parent.

Other features/limitations of Global, Local, and Anonymous replicas:

• Local and Anonymous replicas can only synchronize with the parent replica that created them. If the parent replica is moved, it will receive a new ReplicaID, and will no longer be visible to its Local or Anonymous replicas.
• Local and Anonymous replicas will not be supported for Briefcase replication.
• Local and Anonymous replicas cannot be converted into a Design Master.
• You can create replicas from a Local or an Anonymous replica. The new replica will inherit the same properties as the original replica, except for the ReplicaID. From a Local replica, you can only create a Local replica or an Anonymous replica from an Anonymous replica.

Many replication users requested that a special attribute be provided that would permit a replica to be defined as one where a user could not delete records. This attribute is supported in Microsoft Jet 4.0 replication. An example of when this attribute would be useful is if a full replica is given to a salesperson. The salesperson might be tempted to delete all customer records that were not of interest. However, it would be unfortunate, to say the least, if these deletes were then propagated to all replicas in the replica set. While this can be done via security permissions, it requires the user to understand Microsoft Access security, and to set the appropriate permission on every table. Jet 4.0 provides you with a Prevent deletes replica attribute, which is a much simpler solution.

For additional information about the new features of Microsoft Access 2000, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about the new features of Microsoft Access 2002, click the following article number to view the article in the Microsoft Knowledge Base: For additional information about the new features of Microsoft Office Access 2003, click the following article number to view the article in the Microsoft Knowledge Base: