Creating Partial Replicas

A partial replica is a database that contains a subset of the data contained in the Design Master. You create a partial replica by using either the Partial Replica Wizard or DAO code. It may be easier to create a partial replica by using the wizard and then make modifications to it as needed.

Note The Partial Replica Wizard is available at no cost from the Microsoft Access Developer Forum Web site at http://www.microsoft.com/accessdev/accwhite/prtlrepl.htm. This wizard is also included in the PrtlReplWiz folder on the companion CD-ROM.

The Partial Replica Wizard is a Microsoft Access add-in that displays several dialog boxes to prompt you through the steps required to create a partial replica. You run the add-in from within the Design Master or a full replica that contains the data you want to filter.

The data in a partial replica is defined by an SQL WHERE clause (without the WHERE keyword) on a single table in the Design Master. You can filter records from multiple tables only by using DAO code. When you define the criteria that will make up the WHERE clause, the following restrictions apply:

Using DAO code to create a partial replica gives you much more flexibility and much more control over how you filter the data in the Design Master. Creating a partial replica by using DAO code involves four basic steps:

  1. Create an empty partial replica database. When this step is completed, you have a “template” database, but no data. You create an empty partial replica by using the MakeReplica method of the Database object that refers to the Design Master database, specifying dbRepMakePartial in the options argument. When you create a partial replica in this manner, the partial replica at first contains all the replicable tables in the Design Master but none of the data.

    In the following code, strDMPath is the path to the Design Master database, and strDbPartial is the path to the new partial replica:

    Dim dbs As Database, tdf As TableDef

Set dbs = OpenDatabase(strDMPath)
dbs.MakeReplica Name:=strDbPartial, Options:= dbRepMakePartial
  2. Define filters for the partial replica. There are two types of filters. One type is an SQL WHERE clause (without the WHERE keyword) that contains the selection criteria that defines the records that will appear in a specific table in the partial replica. Define the filter by using the ReplicaFilter property of a TableDef object in the new partial replica. For example, the following code fragment uses the ReplicaFilter property to define a filter based on the EmployeeID field in the Orders table:
    Set tdf = dbs.TableDefs("Orders") 
tdf.ReplicaFilter = "EmployeeID = 3"

    You can use intersection expressions in a single ReplicaFilter property setting. For example, you can create a partial replica containing all orders that fall within a specified starting and ending date. The following code fragment defines a filter that returns all records that occurred between 1/1/95 and 1/31/95:

    tdf.ReplicaFilter = _
	"OrderDate >= #1/1/96# AND OrderDate <= #12/31/96#"

    You can create these types of filters on one or more tables in the database. Synchronizing partial replicas will be significantly faster when you create filters that reference indexed fields. For example, in most circumstances you should define the criteria for the ReplicaFilter property based on a table’s primary key. In the previous example, you’d want to make sure that the OrderDate field is indexed.

    The other type of filter is a Boolean. You set the ReplicaFilter property to True to get all the records and to False to get none.

  3. Decide which records you want from related tables. There are two scenarios that you should take into consideration: the relationships in which your filtered table is on the “many” side, and those in which your table is on the “one” side. Note that this applies only to enforced relationships between tables.

    If your filtered table is on the “many” side of a relationship, Microsoft Jet automatically includes records from tables when required by referential integrity. For example, suppose you set the ReplicaFilter property on the Orders table, as shown in the previous example. The Orders table participates in one-to-many relationships with the Employees, Customers, and Shippers tables; the Orders table is on the “many” side of each of these relationships. Because you cannot have a record in the Orders table that refers to an employee, a customer, or a shipper that doesn’t exist, Microsoft Jet automatically includes all the employees, customers, and shippers in their respective tables for the orders that your filter specifies.

    If your filtered table is on the “one” side of a relationship, referential integrity does not require that records be included from tables on the “many” side. In the case where you have set a filter on the Orders table, which is on the “one” side, Microsoft Jet does not require any of the records in the Order Details table, which is on the “many” side, to be present. However, when you’re creating a partial replica, it’s likely that you’ll want records from the Order Details table as well.

    To include records from the Order Details table, you can set the PartialReplica property on the relationship between the Orders and Order Details tables. The PartialReplica property alerts Microsoft Jet to include records from the “many” side of a one-to-many relationship, if there’s a filter on the “one” side. You must set the PartialReplica property for every enforced relationship for which you want to include records from the table on the “many” side.

    For example, the following code fragment sets the PartialReplica property on the relationship between the Orders table and the Order Details table. In this example, rel is a Relation object:

    If rel.Table = "Orders" And rel.ForeignTable = "Order Details" Then
	rel.PartialReplica = True
End If
  4. Fill the partial replica with data. You use the PopulatePartial method of the Database object that represents the partial replica database. If the database is not opened exclusively, an error occurs when you try to execute this method. Use this method to populate the new partial replica with data before synchronizing for the first time and after you change any partial replica filters. The following code fragment fills a partial replica with data from the full replica specified in the strDMPath argument, which contains the name of the Design Master. In this example, strDbPartial is the path to the partial replica:
    Set dbs = OpenDatabase(strDbPartial, True)
dbs.PopulatePartial strDMPath

    The PopulatePartial method works only with direct connections. It does not support indirect, or Internet/intranet synchronization. If you change the filter for a partial replica, you must use the PopulatePartial method to remove records from the partial replica that matched the old filter and add all records that comply with the new filter.

The following example shows one way to create a partial replica and fill it with data from a Design Master. When you run this function, you’ll see that all replicable tables in the Design Master exist in the partial replica, but that the Orders table contains only those orders entered by the employee whose employee ID is 3, and that this employee is the only employee in the Employees table. The tables whose data is required for referential integrity contain data. Those whose data isn’t required for referential integrity don’t contain data.

To call this function, pass in the name of the Design Master and the name for the new partial replica:

Function CreatePartialReplica(strDMPath As String, _
		strDbPartial As String) As Boolean
	Dim dbs As Database
	Dim tdf As TableDef
	Dim rel As Relation

	On Error GoTo Err_CreatePartialReplica

	' Set a reference to the Design Master.
	Set dbs = OpenDatabase(strDMPath)
	' Create a new partial replica and then close the Design Master.
	With dbs
		.MakeReplica strDbPartial, _
			"Partial replica of " & strDMPath, dbRepMakePartial
		.Close
	End With
	Set dbs = Nothing

	' Create a reference to the new partial replica.
	Set dbs = OpenDatabase(strDbPartial, True)
	Set tdf = dbs.TableDefs("Orders")
	' Set the filter for the new partial replica.
	tdf.ReplicaFilter = "EmployeeID = 3"

	' Specify that Microsoft Jet include records from the "many" side 
	' of a one-to-many relationship.
	For Each rel In dbs.Relations
		If rel.Table = "Employees" And rel.ForeignTable = "Orders" Then
			rel.PartialReplica = True
			Exit For
		End If
	Next rel

	' Fill the partial replica with data.
	dbs.PopulatePartial strDMPath
	CreatePartialReplica = True

Exit_CreatePartialReplica:
	On Error Resume Next
	dbs.Close
	Set dbs = Nothing
	Exit Function

Err_CreatePartialReplica:
	MsgBox "Error: " & Err.Number & " - " & Err.Description
	CreatePartialReplica = False
	Resume Exit_CreatePartialReplica
End Function