Table-level validation: In Access version 1.1, the forms inherited the validations specified in the tables. Validation is now enforced from the engine. This includes:
Normal validation rules/expressions set in tables.
REQUIRED <yes/no>—set if data must be entered in a field when adding a record.
ALLOWZEROLENGTH <yes/no>—Use in conjunction with REQUIRED to enable differentiation between information that doesn't exist (zero length string) and information that may exist, but is unknown (Null).
Rushmore™ optimization: Improved speed when performing optimizable queries. Optimizable queries consist of the ability to use set operations against indexes to quickly find a set of records. See the attached Rushmore Query Optimization in the Jet Database Engine Version 2.0, by Neil Black, for details.
Updateability: Now both sides of joins are updateable. Updateable views become very powerful and intuitive. The 'updatability' matrix is significantly simpler (See Updating Underlying Tables in the on-line help). This new functionality will save developers hundreds of lines of code because they no longer have to hand code this functionality into their applications.
Cascading updates and deletes: With cascading updates and deletes, you can now update or delete a field in a table and have related fields automatically updated or deleted as well. This is a powerful feature that further eliminates the need for code to synchronize data changes.
Query on top values: View the most important data in a query by setting the top values (or top percent) to be returned. This is difficult to do in other query tools.
Elimination of the 4 megabyte transaction limitation: Transaction space could run out in Access version 1.1 on queries that updated a lot of data; his limit has been eliminated.
Support for union queries: Enables you combine fields from two or more tables or queries. When you run a union query, it returns the records from corresponding fields in the included tables or queries. For example, you can find companies that exist in both a customer table and a vendor table.
Support for sub queries: You can now use SQL SELECT statements as subqueries within your expressions in the Criteria row of the Query window. This makes it easy for developers to create complex queries and return only the desired information.
Using Access Basic, you can now define and manage a wide variety of objects, including tables, queries, fields, indexes, relationships, forms, reports, and controls. Microsoft Access version 2.0 provides data access objects in Access Basic to use when programmatically defining, manipulating, and controlling your database system. Data access objects provide the operations generally included in the Data Definition Language, Data Manipulation Language, and Data Control Language components of SQL.
Data access objects provide an object-oriented data access hierarchy. All objects now have methods and properties. For example, Dynaset, Snapshot, and Table now are a type of Recordset. Each Recordset type has its own properties and methods. This is a more structured and intuitive programming paradigm.
The following chart illustrates the new object hierarchy (taken from the Access Building Applications user document).
BeginTrans
Set ds = d.CreateDynaset("select * from authors")
ds.Delete
ds.Close
CommitTrans/Rollback
BeginTrans
d.Execute ("UPDATE Accounts1 SET Balance = Balance + 10")
d.Execute ("UPDATE Accounts2 SET Balance = Balance - 10")
CommitTrans/Rollback
Updateable server views: Server views were strictly read-only in Access version 1.1. Attached views can now be made updateable by creating an index specification on them. This is a big hit for companies that like to use server views for security.
Row reselect: Jet can now deal elegantly with a server based trigger that changes the primary key of a table when a record is inserted (similar to a counter field on SQL Server). It does this by automatically re-selecting the new row. Access used to show these rows as "#DELETED."
Custom update: Updates in datasheets/forms no longer update all columns. Only the columns actually edited will be included in the UPDATE statement sent to the server. This lessens network traffic (especially when memos and OLE objects have not been changed), and prevents gratuitous server-based trigger firing.
Custom insert: Inserts in datasheets/forms no longer insert all columns. Only the non-NULL columns will be included in the INSERT statement sent to the server. This reduces network traffic and prevents overwriting of server-based default values. The server-based default values will appear in the datasheet as soon as the new row is saved.
ODBC Drivers with a CURSOR_COMMIT_BEHAVIOR or CURSOR_ROLLBACK_BEHAVIOR value of zero were read-only in Jet version 1.1, and may now be updateable.
Automatic reconnection: Connections that have been timed out will automatically be re-connected when needed.
Delayed connection: Servers that do not allow multiple statements on a connection (such as SQL Server) require two connections to browse a dynaset. A new delayed-connection algorithm allows small Dynasets (less than 100 rows) to be browsed on a single connection, even against such servers.
Network traffic is a continual concern in client/server computing. Now, Jet enables the user to manage default Access behavior to optimize network traffic for his environment.
Configurable background-population: Two new settings in the server-based MSysConf configuration table control how fast Access does background-population of query results. This setting controls the behavior of all connections to that server.
FetchDelay how often to fetch another chunk of query results <default = 10 seconds> FetchRows how many rows to fetch in a chunk <default = 100 rows>
Programmatic control over data caching: Remote data caching is available through data access objects, using the CacheStart, CacheSize properties, and the FillCache method. Now the application can be highly optimized when repeatedly referencing a heavily used data set. This also allows for more efficient "chunk-fetching" of Dynaset results in data access objects.
New query optimization techniques will have a significant impact on the usability and processing speed of queries to remote tables.
Inferred parameter data types: It's no longer necessary to explicitly declare query parameters against ODBC data. Jet will now infer the parameter type from its surrounding expression context, and send such restrictions to the server for processing. This has been one of the most undiscovered and difficult problems for client/server processing because undeclared parameters caused local query processing in Access version 1.1.
Remote index joins: Remote index joins dramatically speed heterogeneous queries and reduces network traffic. When a large server table is joined to a small local table on an indexed column, the large server table is not completely fetched and joined locally, as in Access version 1.1. Now only the rows needed are fetched, based on the keys in the small local table and any additional restrictions on the remote table are also sent to the server.