ACC: Recovering Data from a Nonmember of a Replica Set

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to recover data from a nonmember of a replica set and synchronize it back into a replica set. The nonmember can be a database with an invalid replica ID, or a database that is not part of the replica set.

MORE INFORMATION

You can use this technique to recover data if the ReplicaID property of a replicated database becomes invalid. Without a valid replica ID, a database is unable to synchronize with the other databases in a replica set. You can also use this technique to extract data from an unreplicated copy of a database, and add the data to the replica set.

Start by linking tables from the nonmember database and use append queries to add new data to the tables in the replica database. Then create select queries to help you identify data that has changed.

NOTE: If your database enforces referential integrity in any of its relationships, then you must append data from a table on the "one" side of a relationship before you can append data from a table on the "many" side.

Appending New Data

1. Synchronize all 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 with a different file name. 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. Select the nonmember database file, 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 will have the number 1 appended to the end. For example, a linked Customers table is named Customers1.

7. Create a new query for each of the linked table/local table pairs using the Find Unmatched Query Wizard. For example, create a query based on the tables Customers1 and Customers.

8. In the "Which table or query contains records you want in the query results?" dialog box, select the linked table, and then click Next.

9. In the "Which table or query contains the related records?" dialog box, select the local table which corresponds to the linked table 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. The new query opens in
        Design view.
    

13. On the Query menu, click Append Query (or Append in version 7.0). In

        the Append dialog box, select the local table in the Table Name box.
    

14. Verify that the Append To field name in the QBE grid is correct in

        every column except the column containing the primary key from the
        local table; clear the Append To cell in that column.
    

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 need to save it.

17. Repeat steps 7-16 for each table that you want to update.

Identifying Data That Has Changed

1. Create a new query in Design view for each one of the local table/linked table pairs. For example, create a query based on the Customers and Customers1 tables.

2. In the query Design window create a join between the two tables based on the Primary Key or other field(s) that uniquely identify each row of data.

3. Add all fields from the linked table to the QBE grid.

4. In the Criteria 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 attached table is People1, the QBE grid looks 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, stepping down one
      line for each new criteria as in the example above.
   
   

5. On the Query menu, click Run.

6. The records that the query returns are those records in the nonmember database that are different in some way 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, and then update the replica database accordingly. Manually reviewing and updating these changes is the surest way to do it, because it can be difficult to determine programmatically which database contains the correct information. For example, if the Address field for customer number 1234 is different in each database, you must decide which database is correct.

7. Repeat steps 1-6 for each table that you want to update.

Synchronizing the Data and Importing Unreplicable Objects

1. When all new and changed data has been updated in the replica, you can delete the table attachments and synchronize with other members of the replica set.

2. If the nonmember database contains local objects that you want to include in the replica, you can import them into the new replica. This applies to all objects, not just tables.

3. When you confirm that the new replica database is working properly, you can replace the nonmember database with the new replica you created.

REFERENCES

For more information about replication and creating replica databases, search the Help Index for "replicas," or ask the Microsoft Access 97 Office Assistant.

For information about obtaining the Microsoft Jet Replication White Paper, please see the following article in the Microsoft Knowledge Base: