In today's global economy, it's the norm for companies to have many offices distributed throughout the country, or even the world. Before Access 95, it was difficult to implement an Access application that would support sharing data among several offices. However, with replication, each office can have a replica of the database. Periodically throughout the day, each office can synchronize its changes with corporate headquarters. How often the synchronization happens depends on the frequency required for data at each location to be current at any given moment.

Sharing data among dispersed users is illustrated by the salesperson example used earlier. This implementation of replication generally involves mobile users who connect to the network after modifying data out on the road. Because only incremental changes are transferred from the Design Master (the original) to the replicas (the copies), and from the replicas to the Design Master, this form of replication makes the mobile computing scenario economically feasible.

Replication can be very effective in reducing network traffic loads. The Design Master can be replicated onto one or more additional servers. Distributed users can then make changes onto one of the additional servers, which significantly improves performance by distributing the processing load throughout the network. Changes made to the data on the additional servers can be synchronized with the main server periodically during the day. How often synchronization occurs depends on the need for data to be current at any moment in time.

Replication is an excellent vehicle for distributing application updates. Design changes can be made only to the Design Master; therefore, as users throughout the organization log on to synchronize with the Design Master, any structural changes to the application are sent to the user. This is much more efficient and effective than giving every user an entirely new copy of the application database each time a minor change is made to the application's schema.

Many people don't think of replication as a means of backing up application data, but replication is extremely well-suited for this task. Ordinarily, to back up an Access database, everyone must log off the system, but that's not necessary with replication. The synchronization process can occur periodically during the day while users are still logged on to the system, and all changes are replicated. Not only is this more efficient than backing up the entire database, but it also ensures that you can quickly be up and running on a backup server if there's a problem on a server.

The Access user interface gives you a series of menu items that allow you to perform all the steps of the replication process. The Tools | Replication menu has the following options: Create Replica, Synchronize Now, Resolve Conflict, and Recover Design Master. These menu options are covered throughout this chapter.

The Windows 95 Briefcase supplies the foundation Access needs for the replication process. Users can simply drag a database file to the Briefcase to replicate it, make changes to the file while on the road, and synchronize the replica with the Design Master when they reconnect to the network. This is done because when Access 97 is installed, it registers a special class ID with the Windows 95 Briefcase. When a database is dragged to the Briefcase, the Briefcase's Reconciler code is called. When the user selects Update Selection or Update All from the Briefcase menu, the Merge Reconciler is called. Briefcase replication is available as an installation option through Windows 95, its successors, and Windows NT 4.0.

The Replication Manager is a sophisticated tool that's part of the Office Developer Edition. It's a mandatory player in the replication process when you're managing many replicas. Besides providing basic replication functionality, the Replication Manager lets you schedule the synchronization of replicas. In fact, the Replication Manager allows you to manage and intricately control all aspects of the replication process. The Replication Manager is covered in more detail in the section “Using the Replication Manager.”

Most aspects of the replication process can also be done by using DAO code, which can be used to make a database replicable, create and synchronize replicas, and get and set properties of a replicable database. DAO can easily be integrated with the other methods of replication. Although requiring the most time and effort on your part, DAO code lets you base replication on events rather than time and give your users a custom user interface for the replication process.

Visual Basic 4.0, Excel for Windows 95, Excel 97, and Visual C++ all support replication using data access objects. You can't perform replication with these products by using either the Briefcase or the Office Developer Edition, however, so it's easier to manage the replication process on a machine that has Access installed.

The Tracking Layer refers to the part of the Jet Engine that's capable of tracking and recording all the changes made to the Design Master and to each of the replicas. It's responsible for making sure changes are available to be transmitted to other replicas.

The Replication Manager gives you the tools needed to support the replication process. It can also be used to generate reports on synchronization activity.

If you use the Briefcase or the Access user interface to manage the replication process, Jet handles the exchange of information between the replicas. If you use the Replication Manager to manage the replication process, the Synchronizer is responsible for monitoring the changes and handling the exchange of data between replicas.

When you're using the Replication Manager, each replica is assigned a synchronizer. The Synchronizer performs either direct or indirect synchronization between the members of a replica set. When synchronization is initiated, the Synchronizer tries to make a direct connection with the target member of the replica set. If both members of the replica set can be opened simultaneously by the Synchronizer, direct synchronization occurs, which means that changes from one member of the replica set are applied directly to the other member.

If the Synchronizer determines that the target replica set member isn't available, then indirect synchronization takes place. There are many reasons why the target replica set member might be unavailable; some possible reasons why a direct connection can't be established include the following:

Regardless of the cause of an indirect synchronization, the Synchronizer for the first member of the replica set leaves a message for the Synchronizer assigned to the member of the unavailable replica set. This message is stored in a shared folder on the network that acts as a drop-box location for the target member. All messages sent while a member of the replica set is unavailable are stored in the drop-box location.

The Synchronizer is configured through the Replication Manager user interface and is covered in more detail in the section “Using the Replication Manager.”

The File System Transport is responsible for supplying messaging services to the Synchronizer.

The Briefcase Reconciler is another integral component of the replication architecture. As mentioned, it makes sure that a database is replicable and manages the merging of changes between the Briefcase replica and the Desktop replica.

Several Windows Registry entries are responsible for helping with the replication process; a couple are shown in Figure 24.1. Notice the Replication Manager and Transporter subkeys under the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet3.5 key. These entries contain important path information used by the Replication Manager and the Synchronizer. The Briefcase Reconciler uses entries under HKEY_CLASSES_ROOTCLSID. Note that although the name has changed to Synchronizer, the key in the registry remains Transporter.

Figure 24.1. Replication and the Windows Registry.

With the Star topology, a single hub periodically synchronizes with the rest of the replicas. The biggest advantage of this topology is simplicity and ease of programming. Another is that data doesn't have to travel very far. Each replica synchronizes with only one other database, the hub. However, this isn't particularly reliable. If the controlling replica is damaged or unavailable, the synchronization process can't take place. Another disadvantage of the Star topology is that the first replica to synchronize with the hub gets no data from the other replicas, but the last replica to synchronize with the hub receives all the data from the others. Issuing two rounds of synchronization can circumvent this problem. Finally, the Star topology can't be used with a large number of replicas because the entire load is placed on the hub.

With the Ring topology, each computer synchronizes with the next computer in the replication chain. There's no central point of synchronization in this scenario. The major advantage of the Ring topology is that the load is distributed throughout the satellite machines. The primary disadvantage is that it might take a long time for changes to be distributed throughout the replicas because there's no central point of synchronization. Furthermore, if one of the replicas in the chain is damaged or unavailable, the replication process stops, but this can be handled by using code that senses the problem and bypasses the unavailable machine.

When the Fully Connected topology is used, every replica synchronizes with every other replica. This topology offers several advantages. Its strongest point is that it makes sure data is the most current at any given moment because data is sent directly from each replica to all the other replicas in the replica set. For this reason, it's the best solution when the data must be as current as possible. Another benefit of the Fully Connected topology is its high level of redundancy. Also, because of the low level of latency (which means that at any given moment, it's likely the data isn't current) in the Fully Connected topology, the effect of any one of the replicas failing is minimal. The Fully Connected topology does have disadvantages, however. It requires the most overhead of any of the topologies because of the network traffic generated as each replica in the set synchronizes with all the others. Furthermore, the replication schedules must be staggered; otherwise, collisions will probably happen as more than one replica tries to synchronize with the same replica.

The Linear topology is similar to the Ring topology except that the chain is never completed. For this reason, the Linear topology has the highest level of latency. The biggest advantage of the Linear topology is the low level of network traffic generated; however, this topology isn't practical for most applications because it takes more time for changes to reach all the replicas in the set.

A Hybrid topology is any combination of the other topologies. In a complex application, it's usually not appropriate to use a single topology by itself. By combining the topologies, you can get exactly the results you need. Figure 24.2 illustrates just one example of a Hybrid topology—a Ring connected with a Star. You should experiment with the different configurations to see which topology best balances processing load, network traffic, and data latency.

During the replication process, the Jet Engine determines whether each table has a field with an AutoNumber data type and a ReplicationID field size. If it doesn't find a field meeting these criteria, it adds a field called s_Guid to the table that uniquely identifies each record. It's identical across replicas.

The Jet Engine also adds two additional fields to each table in the database: s_Lineage and s_Generation. The s_Lineage field stores the IDs of replicas that have updated a record and the last version created by those replicas; the s_Generation field stores information about groups of changes. These fields are visible only if you opt to view system objects (use Tools | Options).

The Jet Engine also adds several tables to your database that track conflicts, errors, and exchanges made between the replica databases. MSysSidetable, MSysErrors, MSysSchemaProb, and MSysExchangeLog are the most useful of these tables; they can be viewed if you have chosen to view system objects.

MSysSidetable tracks tables that have experienced a conflict during the synchronization process. It stores the name of the side table that has the conflicting records.

MSysErrors tracks all unresolved synchronization errors. It's empty when all errors have been resolved. This table can be found in all the replicas.

MSysSchemaProb identifies errors that happened while synchronizing a replica's design. It's visible only if a design conflict has occurred between the user's replica and another replica in the set.

MSysExchangeLog is a local table that stores information about synchronizations that have taken place between the local replica and other members of the replica set.

Several new properties are added to a replicable database. The database's Replicable property is set to True, and this property cannot be modified after it's set to True. The ReplicaID property is a unique ID assigned to each replica. The DesignMasterID property can transfer the Design Master status to another database, which is generally done only if the original master becomes irreparably damaged.

In addition to the properties that apply to the database, two properties can be applied to the tables, queries, forms, reports, macros, and modules in the database: the KeepLocal and Replicable properties. The KeepLocal property, applied to an object before the database is replicated, prevents the object from being copied to the other replicas in the set. The Replicable property, used after a database is replicated, indicates that the object will be replicated.

Another important change made to your tables when replicating a database is that all the AutoNumber fields are changed from incremental to random. Existing records aren't affected, but new keys are generated randomly because that reduces conflicts when the databases are synchronized. If all the copies generate sequential keys, you can see that conflicts will happen when you try to merge changes. By randomly generating primary keys, this conflict is much less likely to take place.

When a database is replicated, its size increases because of the added fields and tables. Generally, this increase isn't a problem. If disk space is at a premium, you should consider this aspect of replication before you decide to build replication into your application.

Choose Tools | Replication | Create Replica. Microsoft Access gives you a warning that it must close the database before proceeding. (See Figure 24.3.)

Figure 24.3. This dialog box warns you that Access must close the database before converting it to a Design Master.

After selecting Yes, you see another dialog box asking whether you want Access to make a backup of the original database before continuing. (See Figure 24.4.) It's always a good idea to back up a database before replicating it because a database can't be returned to its nonreplicable state after it's flagged as replicable.

Figure 24.4. This dialog box prompts you to have Access create a backup of the database before it's replicated.

Next, you're prompted for the name and location of your new replica in the Location of New Replica dialog box. (See Figure 24.5.) After you click OK, the new replica is created and the process is finished.

Figure 24.5. The Location of New Replica dialog box allows you to specify a location and name for the replica.

Your original database is converted to a Design Master, and the replica is assigned the name and location specified in the Location of New Replica dialog box. If the replication process is completed successfully, the dialog box shown in Figure 24.6 appears. Notice that only the Design Master can accept changes to the database structure (schema).

Figure 24.6. This dialog box appears after successful replication of a database.

Instead of using the Access user interface to replicate a database, you can use the Windows 95 Briefcase. All you need to do is drag and drop the database file from Windows Explorer to the Briefcase icon on your desktop; then you see the dialog box shown in Figure 24.7.

Figure 24.7. This dialog box appears after you drag and drop a database to the Briefcase to be replicated.

Except for the final message you get, you see the same series of messages as you do when you choose Replication from the Tools menu in the Access user interface. Only one database can be used to make design changes, such as modifying table structures. The Briefcase dialog box, shown in Figure 24.8, asks which database you want to keep as the Design Master. Generally, you select the Original Copy rather than the Briefcase Copy. You do this because the briefcase copy is generally used only as a means to add, edit, and delete data when on the road.

Figure 24.8. The final dialog box you see during Briefcase replication offers you the choice of which copy you want to use for the Design Master.

To create additional replicas with the Access user interface, follow these steps:

You can also make more replicas by dragging and dropping the Design Master—or any one of the replicas—to the Briefcase. If you drag and drop the Design Master to the Briefcase, you're prompted with a dialog box asking whether you want the Design Master or the replica to be placed in the Briefcase.

To perform synchronization with the Access user interface, follow these steps:

The second way to synchronize replicas is by using the Windows 95 Briefcase. Simply open the Briefcase window and select the database file. On the Briefcase menu, click Update Selection. If you want to synchronize all the replicas in the Briefcase, click Update All to open the Update Briefcase dialog box. (See Figure 24.11.) Click Update, and the synchronization process is finished.

Figure 24.11. The Update Briefcase dialog box appears when you're synchronizing from the Briefcase.

The Replication Manager can be opened by using a Desktop shortcut or through the Windows 95 Start menu. The first time you run the Replication Manager, the Configure Microsoft Replication Manager Wizard appears. (See Figure 24.17.)

Figure 24.17. The Configure Microsoft Replication Manager Wizard.

After the Configure Microsoft Replication Manager Wizard is finished, you can either convert a database to a Design Master or create a new replica. (See Figure 24.23.) You can perform these tasks any time, so you should click Close.

Figure 24.23. The Replication Manager launches for the first time.

In addition to using the Access user interface and the Briefcase to make a database replicable, you can also use the Replication Manager. It offers additional options, such as designating an object as local. Here are the steps:

Just as you can create replicas by using the Access user interface and the Windows 95 Briefcase, you can also create replicas with the Replication Manager. To do so, follow these steps:

Jet 3.5 and Access 97 have introduced partial replication, meaning that only a subset of the data is replicated. This is useful when you have, for example, several sales people and you want each salesperson to have just his or her own data. However, you want all the salespeople to be able to synchronize their changes with other databases on the network. Partial replicas can be created by using the Partial Replica Wizard (you can download it from the Microsoft Internet site) or by using VBA code. The procedure for using VBA code to create a partial replica is in the section “Creating a Partial Replica Using Code.”

Just as you can synchronize replicas with the Access user interface and the Windows 95 Briefcase, you can also use the Replication Manager by following these steps:

Remote Synchronizations

You might be surprised that the Replication Manager map shows only one machine, even though you have many replicas. One icon appears for each Synchronizer involved in the replica set. Figure 24.30 shows two Synchronizers involved in the replication process: TECRA and DELL. The second site was set up by installing the Replication Manager on the second machine. Any replica from the local site can then be moved to the remote site by establishing a connection to the remote site and choosing File | Move Replica. You can locate the Managed Folder at the remote site and move the replica to the remote site's Managed Folder.

Figure 24.30. The Replication Manager map with two Synchronizers.

After both sites appear on the replication map, you can manage synchronizations by using the join line that connects them. You can right-click on the join line and select Synchronize Now or, to establish a schedule for synchronization, select Edit Schedule. Scheduling synchronization is covered in the next section.

Scheduled Synchronizations

Synchronizations can be scheduled between replicas managed by the same Synchronizer, or between replicas managed by two different Synchronizers. To schedule synchronization between replicas managed by the same Synchronizer, right-click on the icon representing the local Synchronizer and select Edit Locally Managed Replica Schedule. (See Figure 24.31.) From here, you can select the days and times when the replicas synchronize.

Figure 24.31. Use the Edit Locally Managed Replica Schedule dialog box to select the days and times when the replicas synchronize.

To schedule the synchronization process between two different Synchronizers, right-click on the join line and select Edit Schedule to open the Edit Schedule dialog box. Here you can schedule the specifics of the synchronization process between the two sites. The shading of each box indicates which Synchronizer initiates that exchange. If the connection can't be made when the exchange is initiated, the dropbox folder keeps a temporary log of the changes. Every 15 minutes, it retries the connection until it's successful.

The synchronization history can be very useful. Besides giving you an audit trail, it helps you analyze the effectiveness of the topology and synchronization schedule you have selected. Three types of logs are kept by the Replication Manager:

To view the local synchronization history, right-click the local machine icon and select View Local Synchronization History; this opens the Synchronization History dialog box. (See Figure 24.32.) It shows you details about the exchange of information between the local replicas.

Figure 24.32. Viewing local synchronization history.

To view the remote synchronization history, select the line joining two Synchronizers and choose View | Synchronization History to open the Synchronization History dialog box. If you want more information about any of the log entries, click Details to open the Synchronization Details dialog box. (See Figure 24.33.)

Figure 24.33. The Synchronization Details dialog box shows the details of a synchronization process.

You can also view properties of the selected Synchronizer. To do this, double-click the Synchronizer and the Replica Properties window appears. This tabbed dialog box gives you important information about the selected Synchronizer.

The following routine renders a database replicable by using code:

Sub MakeReplicable(strTargetDB As String)
    Dim db As Database
    Set db = DBEngine(0).OpenDatabase(strTargetDB, True)
    db.Properties.Append db.CreateProperty("Replicable", _
         dbText, "T")
    db.Close
End Sub

This routine accepts the path to any database as a parameter. It opens up the database exclusively, then appends the Replicable property to the database. This property contains a text value (dbText) set to True.

Unless you do something special, all objects in a database are included in the replication process. The following code illustrates how to flag a database object as local. It sets the KeepLocal property of the tblEmployee table to True.

Sub MakeLocal()
    Const errPropNotFound = 3270
    Dim db As Database
    Dim tdf As TableDef
    On Error GoTo MakeLocal_Err

    Set db = CurrentDb
    Set tdf = db.TableDefs!tblEmployee
    tdf.Properties("KeepLocal").Value = "T"

    Exit Sub
MakeLocal_Err:
    If (Err = errPropNotFound) Then
        tdf.Properties.Append tdf.CreateProperty("KeepLocal", dbText, "T")
    Else
        MsgBox Error
    End If
End Sub

Notice that the code is set up to handle the error that occurs if the object, in this case the tblEmployee table, doesn't already have a KeepLocal property. The error handler appends the property to the object at runtime, and the code then sets the object's KeepLocal property to True.

A new replica can be created with code by using the MakeReplica method. The code looks like this:

Sub MakeReplica(strRepCopy As String)
    Dim db As Database
    Set db = CurrentDb
    db.MakeReplica strRepCopy, ""
    db.Close
End Sub

This code accepts a parameter containing the path and filename for the replica. The MakeReplica method is executed on the database object, and the parameter name becomes the replica's name. The last parameter indicates whether the replica is created as read-only.

You can also create a partial replica using VBA code, as follows:

Sub CreatePartialReplica(strOriginal As String, _
      strReplica As String, _
      strTable As String, _
      strFilter As String)

   Dim db As Database
   Dim tdf As TableDef
   Dim intCount As Integer

   'Open Original Database
   Set db = OpenDatabase(strOriginal)
   'Make Replica Database with dbRepMakePartial constant
   db.MakeReplica strReplica, "Great Replica", dbRepMakePartial
   'Close Original Database
   db.Close

   'Open Replica Database
   Set db = OpenDatabase(strReplica, True)
   Set tdf = db.TableDefs(strTable)
   tdf.ReplicaFilter = strFilter

   'Loop through each table definition
   'If table is a Foreign table then set its PartialReplica
   'property to True
   For intCount = 0 To db.Relations.Count - 1
      If db.Relations(intCount).ForeignTable = strTable Then
         db.Relations(intCount).PartialReplica = True
         Exit For
      End If
   Next intCount
   'Populate Partial Replica
   db.PopulatePartial strOriginal
   db.Close

End Sub

The code begins by creating an empty replica with no filters and no data records. It then sets a filter for a specific table in the database. The PartialReplica property of the table relationships between the selected table and the tables it's related to is set to get records only from the child tables that meet the parent filter. Finally, the partial replica is populated from the full replica.

At times, you might want to finish the synchronization process by using VBA code. The following routine synchronizes the current database with the database whose name is passed as a parameter. The constant dbRepImpExpChanges indicates that you want to perform a two-way synchronization.

Sub Synchronize(strDBToSync As String)
    Dim db As Database
    Set db = CurrentDb
    db.Synchronize strDBToSync, dbRepImpExpChanges
    db.Close
End Sub

You can also handle conflicts by using code. What you do when a conflict is identified is determined by the business needs of your users.

Sub IdentifyConflicts()
    Dim db As Database
    Dim tdf As TableDef
    Dim rstErr As Recordset
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If Len(tdf.ConflictTable) Then
            Set rstErr = db.OpenRecordset(tdf.ConflictTable)
            Do While Not rstErr.EOF
                Debug.Print rstErr.Fields(0).Value
                rstErr.MoveNext
            Loop
            rstErr.Close
        End If
    Next td
End Sub

This routine goes through each table, determining whether something is in the table's ConflictTable property. If the ConflictTable property has something in it, a recordset is opened from the Conflict table. The routine loops through each record of the Conflict table, displaying the value of the first field in the Debug window.