Replicating Part of a Database

So far, this chapter has discussed full replicas, in which all records in all replicas of a replica set are synchronized in their entirety. However, you may only want to replicate part of your data. To do this, you create partial replicas, which contain only a subset of the records in the full replica. With a partial replica, you can set filters and relationships that identify which subset of the records in the full replica you want to synchronize.

By replicating only part of your database, you can restrict access to data. In the case of a sales database, replicating part of a database can help ensure that people in a regional sales office don’t view sales data for other regional offices. In addition, salespersons who carry laptops can filter their data to include only the information that is relevant to their territory. Although you can restrict access to records or filter records when you replicate part of a database, this isn’t a substitute for a security system.

Replicating part of a database also has benefits for replicating data over local area networks (LANs) and wide area networks (WANs). By applying filters and relationships that restrict which data is replicated, you can reduce the amount of data transferred over a LAN or a WAN. This can reduce network traffic and lower telecommunications costs.

To create a partial replica, you can use DAO code or tools provided on the Microsoft Access Developer Forum Web site.

See Also   For more information on the Microsoft Access Developer Forum Web site, see “Accessing Relevant Information on the Internet” in Chapter 2, “Introducing Visual Basic.”

Note   You can synchronize data only between a full replica and a partial replica. You can’t synchronize data between two partial replicas. Also, when you replicate part of a database, you can set restrictions on which records are replicated, but you can’t indicate which fields are replicated.

Creating Partial Replicas

You can use DAO methods and properties to create a partial replica.

Û To create a partial replica

  1. Open with exclusive access the database that contains the data you want to replicate.
  2. Use the MakeReplica method on a Database object, and specify the dbRepMakePartial constant in the options argument.

    See Also   For an example of using the MakeReplica method, see the MakeAdditionalReplica function in “Making Additional Replicas” earlier in this chapter.

  3. Use the ReplicaFilter and PartialReplica properties to set filters and relationships that determine which data to replicate from the full replica.
  4. Use the PopulatePartial method to replicate all records from the full replica that meet the new replica filter criteria.

Note   Once you create a partial replica, you cannot convert it to a full replica. However, if you remove all of the replica filters and replica relationships within the partial replica, it contains all the records of a full replica. However, it still has the same limitations of a partial replica; for example, you can’t synchronize with another partial replica.

Setting Replica Filters

You use a replica filter to specify which subset of records to include from the full replica in the partial replica. To set a filter, use the ReplicaFilter property of the TableDef object. You can set the ReplicaFilter property to the following values.

Value Description
A string A criteria that a record must satisfy to appear in the replicated table. The syntax you use is similar to an SQL WHERE clause, but you cannot specify subqueries, aggregate functions (such as the Count function), or user-defined functions within the criteria.
True Replicate all records.
False (Default) Don’t replicate any records except those required to maintain referential integrity.

For example, to replicate only customer records from the California region, you would use the following code:

Sub ReplicaFilterX(strPartialReplica As String)

	Dim tdfCustomers As TableDef
	Dim strFilter As String
	Dim dbs As Database

	Set dbs = OpenDatabase(strPartialReplica), True

	Set tdfCustomers = dbs.TableDefs("Customers")
	strFilter = "Region = 'CA'"

	tdfCustomers.ReplicaFilter = strFilter

	dbs.PopulatePartial strPartialReplica

	dbs.Close

End Sub

To remove a filter, set the ReplicaFilter property to False. Once you remove all filters and use the PopulatePartial method, no records appear in any replicated tables.

Usually, you reset a replica filter when you want to archive data or replicate a different set of records. For example, when a sales representative temporarily takes over another sales representative’s region, the database application can temporarily replicate data for both regions and then return to the previous filter. In this scenario, the application resets the ReplicaFilter property and then repopulates the databases.

Tip   You can significantly improve synchronization speed by specifying indexed fields in the ReplicaFilter property.

The Role of Referential Integrity

If your database contains relationships that enforce referential integrity, then the Synchronizer automatically replicates related records. For example, suppose you create a new replica with no records in it and set the ReplicaFilter property of the Orders table in the new replica to "Amount > 1000". The next synchronization populates the Orders table in the new replica with all records that have an order amount over $1,000. This also populates records in the Customers table that are related to the Orders records, along with records in the Products table that are related to the records in the Orders table, and so on.

This automatic inclusion only occurs for records that must satisfy referential integrity constraints. For example, because the Customers table has a one-to-many relationship with the Orders table, if one or more records exist in the Orders table for a given customer, the Customers table must have a corresponding record to satisfy the referential integrity rule. Setting the ReplicaFilter property of the Customers table, however, doesn’t automatically include related records in the Orders table. This is because referential integrity rules don’t require a referenced row. To include such rows, use the PartialReplica property described in the following section.

Setting Replica Relationships

In some cases, setting the ReplicaFilter property alone can’t adequately specify which data should be replicated to the partial replica. For example, suppose you have a database in which the Customers table has a one-to-many relationship with the Orders table, and you want to configure a partial replica that only replicates orders from customers in the California region (instead of all orders). You can’t set the ReplicaFilter property on the Orders table to Region = 'CA' because the Region field is in the Customers table, not the Orders table.

You can use the PartialReplica property of the Relation object to specify whether relationships between tables should be considered when populating a partial replica from a full replica. For example, to replicate all orders from customers in the California region, you would create a partial replica and set the ReplicaFilter property for the Customers TableDef object to "Region = 'CA'". Then, set the PartialReplica property to True for the Relation object that corresponds to the relationship between the Orders and Customers tables.

The following code shows how to set the PartialReplica property:

' Assumptions:
' The current open database, dbs, is the partial replica, and a one-to-many
' relationship already exists between the Customers and Orders tables.

' Find the "Customers to Orders" Relation object.
For intI = 0 To dbs.Relations.Count - 1
	If (dbs.Relations(intI).Table = "Customers") _
		And (dbs.Relations(intI).ForeignTable = _
		"Orders") Then
			' Set the Relation object's PartialReplica property to True.
			dbs.Relations(intI).PartialReplica = True
			Exit For
	End If
Next intI

If you have a second requirement to replicate all orders greater than $1,000, regardless of the region with which they are associated, you can set the ReplicaFilter property of the Orders table to "Amount > 1000". Synchronizing the partial replica with the full replica populates the Orders table with all orders greater than $1,000 from all regions, in addition to all orders from the California region.

Keep the following points in mind when setting replica filters and relationships:

  • If you have set a replica filter and a replica relationship on the same table, the two act in combination as a logical Or operation, not a logical And operation. For instance, in the preceding example, the records exchanged during synchronization are all orders greater than $1,000 or all orders from the California region, not all orders from the California region that are over $1,000.
  • It makes no difference in which order you add replica filters and replica relationships.
  • Although it’s possible to set complex filters and replica relationships, this isn’t recommended. Use the ReplicaFilter and PartialReplica properties to place general restrictions on which data is replicated.

Repopulating Partial Replicas

When you synchronize a partial replica with a full replica, it’s possible to create “orphaned” records in the partial replica. For example, suppose you have a Customers table with its ReplicaFilter property set to "Region = 'CA'". If a user changes a customer’s region from CA to NY in the partial replica, and then a synchronization occurs, the change is propagated to the full replica, but the record containing NY in the partial replica is orphaned because it now doesn’t meet the replica filter criteria.

To solve the problem of orphaned records, you can use the PopulatePartial method. The PopulatePartial method transfers any unpropagated changes to the full replica, removes all records in the partial replica, and then repopulates the partial replica based on the current replica filters. Even if your replica filters have not changed, the PopulatePartial method always repopulates the partial replica by clearing all records in the partial replica.

Generally, you use the PopulatePartial method when you create a partial replica and whenever you change your replica filters.

Û To change replica filters

  1. Before you change the replica filters, use the Synchronize method to synchronize your full replica with the partial replica.

    See Also   For more information on the Synchronize method, see “Synchronizing Replicas” later in this chapter.

  2. Use the ReplicaFilter and PartialReplica properties to make changes to the replica filters and relationships.
  3. Use the PopulatePartial method to remove all records from the partial replica and transfer all records from the full replica that meet the new replica filter criteria.

If a replica filter has changed and you use the Synchronize method without first using the PopulatePartial method, a trappable error occurs.

Note   Although the PopulatePartial method performs a one-way synchronization from the partial replica to the full replica before it removes and repopulates the partial replica, it’s still a good idea to use the Synchronize method before you use the PopulatePartial method. If the Synchronize method fails, a trappable error occurs. You can use this error to decide whether or not to proceed with the PopulatePartial method (which removes all records in the partial replica). If you use the PopulatePartial method by itself and an error occurs during synchronization, records in the partial replica are still removed.