Programming Microsoft Access 2000 (Microsoft Programming Series)

One of the most significant upgrades in Access 2000 is the introduction of the JRO model for programmatically controlling replication. Since some new features, such as the Visibility property, are not available with the traditional DAO programmatic interface—even in its latest upgrade (version 3.6)—you should definitely learn the new way of programming replication.

Jet-SQL Server Bidirectional Replication

Access 2000 offers bidirectional replication between Jet and Microsoft SQL Server replicas. This kind of transfer requires Jet 4 and SQL Server 7. Bidirectional transfer means that SQL Server can serve as a central repository for a disconnected set of Access applications. Mobile workers with Access applications can transfer updates to a central database and download the latest changes from a headquarters database. You need to start with a SQL Server replica or upsize an Access replica to SQL Server.

When you set up a Jet-SQL Server replica set, a SQL Server replica must be at the hub and Jet replicas can function at the spokes. The Jet replicas can exchange content bidirectionally with the SQL Server hub replica. However, Jet replicas cannot exchange content bidirectionally with other Jet replicas at the spokes. The SQL Server hub replica must always serve as an intermediary between Jet replicas at the spokes. Since SQL Server is just a database engine and not a full application development environment like Access, you cannot replicate Access-specific application objects, such as forms and reports, to the SQL Server hub. Nevertheless, this kind of design compensates by offering the other special advantages of SQL Server systems, such as client/server processing and multiprocessor scalability. (See "Implementing Merge Replication to Access Subscribers" in SQL Server Books Online for more details on the behavior of Jet-SQL Server replica sets.)

Column-Level Updates

One important way to improve the productivity of workers using a replicable database is to reduce the number of conflicts. Access 2000 introduces column-level updates to minimize collisions between two replicas. With prior Access versions, updates from two replicas collided if they changed the same record—even if they changed two different fields on the same record—because the lowest level of update tracking for replicas was the row. With Access 2000, you can create replicas that detect changes down to the level of individual fields. Therefore, one user can change a customer's fax number in one replica while another user changes the street address of the contact person for the same customer in another replica. When the two replicas synchronize, no conflicts result.

Column-level tracking is the default setting for all new replicas. If you prefer, you can choose the traditional row-level tracking. When you update a replicable database from an earlier version of Access, it retains row-level tracking. Since there is a performance and size penalty for column-level tracking, you should not use it if conflicts are highly unlikely. The column-level tracking feature works in conjunction with SQL Server replicas as well.

Replica Visibility Levels

Access 2000 introduces three degrees of visibility for replicas: global, local, and anonymous. You can control the visibility of a replica as a property in JRO. You cannot change the visibility of a replica after you create it.

Replicas with global visibility function like traditional Access replicas. They can replicate with any other replica, and they are visible throughout a replica set. Local and anonymous replicas have special roles that allow a reduction in their size relative to the traditional global replicas. Replicas created from local and anonymous replicas share their parent's visibility property setting, except that they have a unique ReplicaId property. You cannot create a design master replica from either a local or an anonymous replica.

Local replicas are visible only to their parent, and they exchange content exclusively with their parent. Either the parent or the local replica can initiate an exchange. In addition, the hub parent for a local replica can schedule recurring synchronizations with a local replica. Local replicas cannot exchange information directly with other replicas in a replica set. However, changes to a local replica can propagate from a local replica throughout a replica set by passing through the parent. Any conflicts between a local replica and its hub parent always result in the parent winning.

Anonymous replicas are for distribution across a Web-based (FTP or HTTP 1.1) connection. For successful synchronization of an anonymous client replica with a Web-based hub replica, the original anonymous replica must have as its source a global replica managed by the Replication Manager on the web server. You can distribute copies using any appropriate means (for example, over the Web or using a CD). Like the local replica, the anonymous replica can synchronize only with its parent, but the parent cannot schedule replications with its anonymous children replicas. Exchanges must always be initiated by the anonymous replica to its parent. If any conflicts occur during synchronization, the parent replica always wins.

Priority-Based Conflict Resolution

Access 2000 has a new default conflict resolution rule. The earlier releases of Access resolved conflicts between two replicas in favor of the replica that changed a record the most. If two replicas changed a record an equalnumber of times, the replica with the lowest ReplicaId property won. The new default conflict resolution scheme employs a variation of the 800-pound gorilla rule: The replica with the highest priority wins. Priority property settings for replicas can range from 0 through 100. Again, if two replicas have an equal priority, the one with the lowest ReplicaId property wins. This new rule has the advantage of being consistent with the one in SQL Server 7 replication.

A replica's Priority property setting is read-only after you create it. By default, the initial replica for a database has a setting of 90. Any global replica based on another replica has a priority that is 90 percent of the initial one. Anonymous and local replicas have their Priority property forced to 0. Replicas copied via MS-DOS or the CompactDatabase method have a priority that matches the original. Converted databases have a priority of 90. Otherwise, you can assign a Priority property anywhere in the legitimate range when you initially make a database replicable. All subsequent global replicas must have a Priority value that is less than or equal to their parent (unless the person creating the replica is a member of the Admins group or is an owner of the database).

Miscellaneous Refinements

A set of miscellaneous adjustments rounds out the new replication functionality in Access 2000. A couple of them simplify such common tasks as restricting the behavior of a replica or reconciling synchronization errors. In another case, a design change can affect the optimal method of distributing application design changes.

The new Prevent Deletes replica (described earlier) prohibits users from deleting records. This feature is an easy way to ensure that inexperienced users cannot inadvertently delete important content. The replica can still have deletions propagated to it from another replica, such as one managed by a database administrator.

As mentioned previously, you can now process synchronization conflicts and errors with the same interface. The Conflict Resolution wizard presents both conflicts and errors. (See Figure 11-2.) This removes the need for separate processing of both with different interfaces. In addition, the wizard works with both Jet 4 and SQL Server 7 replication.

Deletions always have a higher priority than any change associated with a record in synchronization. This is true for all versions of Access, but with Access 2000, records losing to a delete add an entry to the conflict table for a user table. Prior versions simply ignored updates that lost to a delete.

Access 2000 introduces support for cascading through conflicting records. If the primary keys in two replicas conflict, one wins and the other loses. Records in other related tables also lose. When you fix the primary key in the losing replica, your fix in Access 2000 cascades through to the related records in other tables so they do not require individual adjustment. Earlier Access versions required independent fixes to the primary table and its related tables.

Access 2000 has a new storage format that can affect how you decide to distribute software changes for a replicated project. With prior releases, you could synchronize changes to individual Access objects, such as forms and reports. With Access 2000, Access objects such as forms, reports, modules and pages are either in a single binary large object or in the separate "project.adp" file. This new storage design forces the replication of all objects if it is necessary to update any object. If this solution is not attractive, you can make the Access project in the design master not replicable. Then you are free to distribute design changes by other means (for example, using a CD).

Figure 11-2. The Conflict Resolution wizard's Replication Conflict Viewer, which displays both conflicts and errors.