This article applies only to a Microsoft Access database (.mdb).
Storing characters in UnicodeOne 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.
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 typesTo 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
CREATE TABLE TableName (FieldName TEXT)
CREATE TABLE TableName (FieldName MEMO)
CREATE TABLE TableName (FieldName TEXT (100))
CREATE TABLE TableName (FieldName CHAR (100))
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:
CREATE TABLE TableName (FieldName1 IDENTITY (10, 5), FieldName2 CHAR,
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:
ALTER TABLE TableName ALTER COLUMN FieldName SET IDENTITY (2,4)
A new Microsoft Jet 4.0 data typeDecimal 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 typesIn 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 typesAll 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:
CREATE TABLE TableName (FieldName CHARACTER(255) WITH COMPRESSION)
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 extensibilityThere 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.
SecuritySupport 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:
CREATE / ADD / ALTER / DROP USER / GROUP
To create users or groups (one or more users or groups can be created at one time):
CREATE USER UserName1 Password1, UserName2 Password2
CREATE GROUP GroupName1, GroupName2
ADD USER UserName1, UserName2 TO GroupName
DROP GROUP GroupName
DROP USER UserName
DROP USER UserName FROM GroupName
ALTER DATABASE PASSWORD NewPassword OldPassword
ALTER USER UserName PASSWORD NewPassword OldPassword
GRANT SELECT ON TABLE TableName TO UserName
REVOKE SELECT ON TABLE TableName FROM UserName
GRANT SELECT, INSERT ON TableName to UserName, GroupName
GRANT DROP ON OBJECT QueryName TO UserName, GroupName
REVOKE DROP ON OBJECT QueryName FROM UserName, GroupName
- ALL PRIVILEGES
There are also a few privileges that are specific to the database itself. They are:
ViewsSupport 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:
CREATE VIEW ViewName AS SELECT * FROM TableName
CREATE VIEW ViewName AS SELECT FieldName1, FieldName2 FROM TableName
CREATE VIEW ViewName (ViewColumnName1, ViewColumnName2) AS SELECT FieldName1, FieldName2 FROM
CREATE VIEW ViewName (ViewColumnName1, ViewColumnName2) AS SELECT TableName1.FieldName1,
TableName2.FieldName1 FROM TableName1, TableName2 WHERE TableName1.FieldName2 = TableName2.FieldName3
To delete an existing view, use the DROP VIEW syntax. For example:
DROP VIEW ViewName
ProceduresSupport 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:
CREATE PROCEDURE ProcedureName AS UPDATE TableName SET (FieldName1 =
FieldName1 * 10) WHERE FieldName1 < 5
CREATE PROC ProcName (Param1) AS DELETE FROM TableName WHERE FieldName1 <
CREATE PROC ProcName (Param1 Integer) AS DELETE FROM TableName WHERE
FieldName1 < Param1
To delete a procedure, simply reference the procedure name in the DROP PROCEDURE statement. For example:
DROP PROCEDURE ProcName
To execute an existing procedure, use the EXECUTE verb, followed by the procedure name:
EXECUTE ProcName (5)
TransactionsMicrosoft 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:
TablesThe 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:
<check constraint definition> ::= CHECK <left paren> <search condition>
CREATE TABLE TableName1 (FieldName DOUBLE);
INSERT INTO FieldName VALUES (100);
CREATE TABLE TableName2 (FieldName1 IDENTITY (100, 10), FieldName2
VARCHAR(10), FieldName3 VARCHAR(15), FieldName4 DOUBLE, CHECK (FieldName4
<= (SELECT SUM (FieldName) FROM TableName1)));
INSERT INTO TableName2 (FieldName2, FieldName3, FieldName4) VALUES ('John',
INSERT INTO TableName2 (FieldName2, FieldName3, FieldName4) VALUES ('Joe',
CREATE TABLE TableName2 (FieldName1 IDENTITY (100, 10), FieldName2
VARCHAR(10), FieldName3 VARCHAR(15), FieldName4 DOUBLE);
ALTER TABLE TableName2 ADD CONSTRAINT (FieldName4 <= (SELECT SUM
(FieldName) FROM TableName1));
Referential integrityDeclarative 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:
CREATE TABLE Customers (CustomerID INTEGER PRIMARY KEY, CompanyName NCHAR
CREATE TABLE Orders (OrderID INTEGER PRIMARY KEY, CustomerID INTEGER,
ShipAddress NCHAR VARYING (255), CONSTRAINT FKOrdersCustomerID FOREIGN KEY
(CustomerID) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
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:
CREATE TABLE Orders (OrderID INTEGER PRIMARY KEY, CustomerID INTEGER,
ShipAddress NCHAR VARYING (255), CONSTRAINT FKOrdersCustomerID FOREIGN KEY
(CustomerID) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL
Fast foreign keysTypically, 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 tablesThe 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:
CREATE TABLE TableName (FieldName1 INTEGER, FieldName2 CHAR)
ALTER TABLE TableName ALTER COLUMN FieldName1 CHAR
<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column
<alter column action> ::= <set column default clause> | <drop column
default clause> | <column definition>
<column definition> ::= <column name> <data type> [ <default clause>] [
<column constraint definition> ]
<column constraint definition> ::= [ <constraint name definition> ] |
<unique specification> | <references specification> | <check constraint
Access to external dataMicrosoft 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 dBASERead/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:
FoxProAccess 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:
ExchangeSupport 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.
ExcelThe 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/HTMLThe 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.
LotusNo 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 controlThe 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 additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
User listThe 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 is also useful in isolating problems with database corruption that is associated with the activities of a specific user.
Lock promotionWhen 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 lockingWith 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
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:
Bi-directional replication with Microsoft SQL ServerWith 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).
Resolving replication conflictsIn 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.
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
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.
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 resolutionIn 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 resolutionIn 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 formatIn 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 visibilityMicrosoft Jet 4.0 replication defines three degrees of visibility for replicas. A replica's visibility can be defined as:
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.
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.
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.