3 4
Database replication lets users in multiple locations update data in an application while allowing design changes only in a single copy of the database. This ensures that the integrity of the database design is preserved. When a database is replicated, two or more copies of the database are created; one is the Design Master, which is the only database in which design changes can be made, and any others are replicas. You can create as many replicas as you want. The Design Master and the replicas together form a replica set, which has a unique replica set identifier.
Replicas and Synchronization
Several techniques are available for synchronizing data in Access databases, as described in the following list. Note that database synchronization is useful only when the users of the Design Master and all the replicas are connected to the same network (at least some of the time).
Briefcase replication You can use the Windows Briefcase component to keep a database up-to-date. This approach is used primarily in single-user situations in which data is taken off line on a laptop and later synchronized with the desktop database.
Database replication You can use commands on the Tools menu in Access to create and synchronize database replicas.
Replication Manager This component of Microsoft Office XP Developer is a full-featured tool for managing replicas, setting synchronization schedules, and viewing members of replica sets.
Programmatic replication Using components of the Jet and Replication Objects (JRO) object model, you can write code to compact databases, refresh data from the cache, and create and maintain replicated databases.
SQL Server replication Using a project with a SQL Server back end, the SQL Server administrator can set up replication. Users can work on data access pages off line and later synchronize them with the SQL Server database.
To replicate a database, follow these steps:
Figure D-1. Close the database to be replicated.
Figure D-2. Create a backup before converting a database to a Design Master.
Figure D-3. Save the first replica in the replica set.
Figure D-4. This message will appear after you create a replica.
Removing a Database Password
To remove a database password, follow these steps:
The original database is now a Design Master. If you look at the database objects, you’ll see that the original objects all have new icons, indicating that they’re replicated objects. You’ll also see several new replication tables. (See the "Replication Reference" section later in the chapter for information on these tables.) Figure D-5 shows the Tables tab of the Crafts 2002 database Design Master.
Figure D-5. Here’s a Design Master of the Crafts 2002 database with new replication tables.
note
After replicating a database, when you create a new object in that database, the Save As dialog box will have a Make Replicable check box (as shown in Figure D-6 ). Select the check box to make the new object replicable (so it will be replicated to the replicas); leave it unchecked to keep the object local to the Design Master database.
Figure D-6. Save a new table in a Design Master database.
You can make design changes (such as adding, deleting, or modifying database objects) only in the Design Master of a replica set; you can modify data either in the Design Master or in the replicas. As users add, change, or modify data or as the database designer changes database objects, the Design Master and the various replicas in the replica set will get out of synch.
To synchronize a database, follow these steps:
Figure D-7. Close open databases before synchronization.
Figure D-8. Select a replica for synchronization.
Figure D-9. This message should appear after you synchronize a replica.
Most of the time, Access will successfully resolve replication conflicts using the priority numbers assigned to members of the replica set. However, if you don’t receive the success message shown in the figure, you should check for synchronization errors to find out what went wrong. To check for synchronization errors, choose Tools, Replication, Resolve Conflicts to open the Conflict Viewer.
When you replicate an Access database, a number of changes are made to the database. These changes are visible only if you’ve checked the System Objects check box on the View tab in the Options dialog box. The fields listed in Table D-1 are added to the tables, and the tables listed in Table D-2 are added to the database. The properties listed in Table D-3 might also be added to the database.
Table D-1. New replication fields
Field | Description |
s_GUID | A globally unique identifier (GUID) for each record |
s_Lineage | A binary field that contains information about the history of changes to each record |
s_ColLineage | A binary field that contains information about the history of changes to each field |
s_Generation | A field that stores information regarding groups of changes |
The information in these fields can’t be changed.
An additional field named Gen_Fieldname will be added for each Memo or OLE Object field in the database.
A number of tables are added to a database when it is replicated. The most significant of these tables are listed in Table D-2.
Table D-2. Replication tables
Table | Description |
MSysSideTables | A table used to store information about conflicts between the user’s replica and another replica in the replica set. This table isn’t replicated. It’s supplied for information only, and its contents can be modified or deleted by custom conflict-resolution routines or by the user. All side tables are named table _conflict, where table is the original name of the table. (This table exists only if conflicts have occurred.) |
MSysSchemaProb | A table that is present only when an error has occurred while the design of a replica is being updated. It provides additional details about the cause of the error. This is a local table and isn’t replicated. |
MSysReplicas | A table that stores details, such as the Replica ID and path, of all known replicas in the replica set. This table appears in all members of the replica set, but it is not replicated. |
MSysTranspAddress | A table that stores addressing information for the Synchronizer and defines the set of synchronizers known to this replica set. This replicated table appears in all members of the replica set. |
MSysTombstone | A table that stores information about deleted records and allows deletions to be dispersed to other replicas during the synchronization process. This table appears in all members of the replica set, but it is not a replicated table. |
MSysRepInfo | A table that stores information about the entire replica set, including the identity (GUID) of the Design Master. The table contains a single record. This replicated table appears in all members of the replica set. |
MSysExchangeLog | A table that stores information about replica synchronizations that have taken place. It is a local table and isn’t replicated. |
The information in these tables (except for MSysSideTables) can’t be changed.
Table D-3. Replication properties
Property | Description |
Replicable or ReplicableBool | A database or object property. When the property is set to T (or to True for ReplicableBool), the database, table, or query is replicable. The Replicable and ReplicableBool properties can be used interchangeably. |
KeepLocal | A property appended to a table or query. When the property is set to T, the object should not be replicated when the database is replicated. An object that is already replicated can’t have its KeepLocal property set to T. |
ReplicaID | A property that provides each member of the replica set with unique identification. This property is read-only and is stored in the MSysReplicas system table. |
DesignMasterID | The ReplicaID of the Design Master. It is stored in the MSysRepInfo system table as SchemaMaster. |
ColumnLevelTracking | A database or table property. When the property is set to True (the default), conflicts are tracked at the column level of a table. |
note
When a database is replicated, any incremental AutoNumber fields in the tables will be changed to random numbering. Existing records will keep their values, but new records will have random AutoNumber values.