ACC2000: How to Recover Data from a Nonmember of a Replica Set (208323)

MORE INFORMATION

You can use the technique in this article for the following actions:

• To recover data when the ReplicaID property of a replicated database becomes invalid. Without a valid replica ID, a database cannot synchronize with the other databases in a replica set.
• To extract data from an unreplicated copy of a database and to add the data to the replica set.

To recover data from a nonmember of a replica set, start by linking the tables from the nonmember database. Then, use append queries to add the new data to the tables in the replica database. Finally, create select queries to help you identify the data that has changed.

NOTE: If your database enforces referential integrity in any of its relationships, first append the data from a table on the "one" side of a relationship. Then, append the data from the table on the "many" side.

How to Link the Tables and Append the New Data

1. Synchronize all the members of the replica set so that the data is current in all databases.
2. Create a new replica database in the same folder as the nonmember database, but give the new replica database a different file name than the nonmember database. You can create the replica from the Design Master database or from another replica.
3. Open the new replica database.
4. On the File menu, point to Get External Data, and then click Link Tables.
5. Click the nonmember database, and then click Link.
6. Link all the replicated tables in the nonmember database. The replicated tables are those that have a matching table name in the current database. The linked tables will keep the same names, but they will have the number 1 appended to the end of their names. For example, a linked Customers table will be named Customers1.
7. Use the Find Unmatched Query Wizard to create a new query for each of the local table/linked table pairs. For example, create a query that is based on the Customers and Customers1 tables.
8. In the Which table or query contains records you want in the query results dialog box, click the linked table, and then click Next.
9. In the Which table or query contains the related records dialog box, click the local table that corresponds to the linked table that you chose in the previous dialog box, and then click Next.
10. In the What piece of information is in both tables dialog box, select the primary key field from each table, click the <=> button to join the fields, and then click Next.
11. In the What fields do you want to see in the query results dialog box, move all the fields to the Selected fields box, and then click Next.
12. In the What would you like to name your query dialog box, click Modify the design, and then click Finish. Note that the new query opens in Design view.
13. On the Query menu, click Append Query. In the Append dialog box, click the local table in the Table Name box.
14. Verify that the Append To field name in the query design grid is correct in every column except the column that has the primary key from the local table. For that column, clear the Append To field.
15. On the Query menu, click Run to append the new records from the linked table to the local table.
16. Close the query. You do not have to save it.
17. Repeat steps 7 through 16 for each table that you want to update.

How to Identify the Data That Has Changed

1. Create a new query in Design view for each of the local table/linked table pairs. For example, create a query that is based on the Customers and Customers1 tables.
2. In the query design grid, create a join between the two tables based on the Primary Key or based on another field or fields that uniquely identify each row of data.
3. Add all the fields from the linked table to the query design grid.
4. In the Criteria row for each of the fields, type an expression to indicate that the data is not equal to the data in the corresponding field name of the local table. For example, if the local table is People and the linked table is People1, the query design grid will look as follows:

          Field:    Name               Addr               City
          Table:    People1            People1            People1
          Criteria: <>[People].[Name]
          or:                          <>[People].[Addr]
          or:                                             <>[People].[City]
   						

   NOTE: You must type each expression in its own row. Step down one line for each new criteria, as shown in the example.
5. On the Query menu, click Run.
6. The query returns the records in the nonmember database that are different from the corresponding records in the replica database. You must decide if the record in the nonmember database is correct, or if the record in the replica database is correct. Then, you must update the replica database accordingly. Because this is difficult to do programmatically, the best way to do this is to look at each record. For example, if the Address field for customer 1234 is different in each database, you must decide which database is correct.
7. Repeat steps 1 through 6 for each table that you want to update.

How to Synchronize the Data and Import Unreplicable Objects

1. When you have updated all the new and changed data in the replica, you can delete the table links and synchronize with other members of the replica set.
2. If the nonmember database has local objects that you want to include in the replica, you can import these objects into the Design Master. You can do this for all objects, not just tables.
3. When you have confirmed that the new replica database is working properly, replace the nonmember database with the new replica that you created.