Advanced: Requires expert coding, interoperability, and multiuser
skills.
This article applies only to a Microsoft Access database (.mdb).
MORE INFORMATION
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
CREATE TABLE TableName (FieldName TEXT)
is equivalent to
CREATE TABLE TableName (FieldName MEMO)
and aligns with the SQL Server definition and syntax for Text. This
next example:
CREATE TABLE TableName (FieldName TEXT (100))
is equivalent to
CREATE TABLE TableName (FieldName CHAR (100))
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:
CREATE TABLE TableName (FieldName1 IDENTITY (10, 5), FieldName2 CHAR,
FieldName3 CHAR)
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:
ALTER TABLE TableName ALTER COLUMN FieldName SET IDENTITY (2,4)
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:
CREATE TABLE TableName (FieldName CHARACTER(255) WITH COMPRESSION)
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:
CREATE / ADD / ALTER / DROP USER / GROUP
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):
CREATE USER UserName1 Password1, UserName2 Password2
CREATE GROUP GroupName1, GroupName2
To add a user to an existing group:
ADD USER UserName1, UserName2 TO GroupName
To drop a group, or to drop user(s), or to drop users from a group:
DROP GROUP GroupName
DROP USER UserName
DROP USER UserName FROM GroupName
To create or modify a database password:
ALTER DATABASE PASSWORD NewPassword OldPassword
To modify a user's password:
ALTER USER UserName PASSWORD NewPassword OldPassword
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:
The basic form of the Grant/Revoke syntax is shown in the
following examples:
GRANT SELECT ON TABLE TableName TO UserName
REVOKE SELECT ON TABLE TableName FROM UserName
More than one privilege may be granted or revoked at one time for more
than one user or group:
GRANT SELECT, INSERT ON TableName to UserName, GroupName
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:
GRANT DROP ON OBJECT QueryName TO UserName, GroupName
REVOKE DROP ON OBJECT QueryName FROM UserName, GroupName
The types of privileges specified by ANSI that can be granted or
revoked are as follows:
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:
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
TableName
CREATE VIEW ViewName (ViewColumnName1, ViewColumnName2) AS SELECT TableName1.FieldName1,
TableName2.FieldName1 FROM TableName1, TableName2 WHERE TableName1.FieldName2 = TableName2.FieldName3
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:
DROP VIEW ViewName
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:
CREATE PROCEDURE ProcedureName AS UPDATE TableName SET (FieldName1 =
FieldName1 * 10) WHERE FieldName1 < 5
CREATE PROC ProcName (Param1) AS DELETE FROM TableName WHERE FieldName1 <
Param1
CREATE PROC ProcName (Param1 Integer) AS DELETE FROM TableName WHERE
FieldName1 < Param1
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:
DROP PROCEDURE ProcName
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:
EXECUTE ProcName
If there are parameters defined in the procedure, parameter values
follow the procedure name as a comma separated-list enclosed in parentheses:
EXECUTE ProcName (5)
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:
BEGIN TRANSACTION
To end a transaction and commit the work done within the transaction
(statements executed since the transaction began), use:
COMMIT
or
COMMIT TRANSACTION
To end a transaction and rollback statements executed since the
transaction began, use:
ROLLBACK
or
ROLLBACK TRANSACTION
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:
<check constraint definition> ::= CHECK <left paren> <search condition>
<right paren>
For Example:
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',
'Doe', 100);
INSERT INTO TableName2 (FieldName2, FieldName3, FieldName4) VALUES ('Joe',
'Blowe', 101);
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:
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 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:
CREATE TABLE Customers (CustomerID INTEGER PRIMARY KEY, CompanyName NCHAR
VARYING (50))
Now consider the following definition of a table called Orders, which
defines a foreign key relationship referencing the primary key of the Customers
table:
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
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:
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
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:
CREATE TABLE TableName (FieldName1 INTEGER, FieldName2 CHAR)
The data type of the field FieldName1 can be changed using the ALTER
COLUMN syntax as follows:
ALTER TABLE TableName ALTER COLUMN FieldName1 CHAR
Additional syntax for the ALTER TABLE:
<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column
action>
<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
definition
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:
230125
How to use dBASE data with Access and Jet
230126 How to use Paradox data with Access and Jet
290867 How to use dBASE data with
Access and Jet
286246 How to use Paradox data with
Access and Jet
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:
225861
No option to import,
link, or export to the FoxPro file type
286224 No option to import,
link, or export to the FoxPro file type
824264 You cannot connect directly to FoxPro databases in Access 2003
197893 How to use Visual FoxPro DBC
files in Microsoft Access 2000
286815 How to use Visual FoxPro DBC
files in Microsoft Access 2002
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:
180105
The file formats that are
supported by Access 2000 when you import, export, and link
283294 Supported data sources
for importing, exporting, and linking in an Access 2002 database
826507 Supported data sources for importing, for exporting, and for linking in a Microsoft Office Access 2003 database
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:
198755
How to check who logged into database with Jet UserRoster in Access
2000
198756 How to use Connection Control to
prevent user log on at run time in Access 2000
285822 How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2002 or in Access 2003
287655 How to use Connection Control to
prevent users from logging on at run time in Access 2002
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:
306435
Jet 4.0 row-level locking is
not available with DAO 3.60
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.
- 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:
190766
Jet 4.0 replication white papers available in MSDN online library
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:
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.