Show All

Hide All

Note  The information in this topic applies only to a Microsoft Access database (.mdb).

After you've converted a database into a Design Master (Design Master: The only member of the replica set in which you can make changes to the database structure that can be propagated to other replicas.), you can't convert it back to its former status as a regular database. However, if you no longer want to use replication (replication: The process of copying a database so that two or more copies can exchange updates of data or replicated objects. This exchange is called synchronization.) and want to decrease the size of a replicated database, you can create a new, regular database that contains all of the objects and data in your replicated database without the additional system fields, tables, and properties associated with replication.

Note  For each table in the new database, create the same index used in the replica (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.) table. If the s_GUID (GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs.) is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) (and foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in other tables refer to it), you'll want the s_GUID field in the new table. If the s_GUID field is not used as a primary key, there is no reason to preserve it in the new table. Don't copy the s_Lineage and s_Generation fields to the new table.

1. Create a blank database and open it.
2. On the File menu, point to Get External Data, and then click Import.
3. Click the replica that contains the database objects that you want in the regular database, and then click Import.
4. Click the database objects— except tables— that you want to import into the new database.
5. Click OK to import these objects.
6. In the old database, for each table in the replica, create a Make Table Query that takes all the data in the old table and creates a table in the new database with the same data.

   How?

   1. Create a query, selecting the tables or queries that contain the records you want to put in the new table.

      How?

      1. In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects, and then click New on the Database window toolbar.
      2. In the New Query dialog box, click Design View, and then click OK.
      3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
      4. Double-click the name of each object you want to add to the query, and then click Close.
      5. Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
      6. To view the query's results, click View on the toolbar.
   2. In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), click the arrow next to Query Type on the toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), and then click Make Table. The Make Table dialog box appears.
   3. In the Table Name box, enter the name of the table you want to create or replace.
   4. Do one of the following:

      Click Current Database.

      Click Another Database, and then either type the path of the database where you want to put the new table or click Browse to locate the database.

   5. Click OK.
   6. Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want in the new table.
   7. In the Criteria cell for the fields that you've dragged to the grid, type the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
   8. To preview the new table before you create it, click View on the toolbar. To return to query Design view and make changes or run the query, click View on the toolbar again.
   9. To create the new table, click Run on the toolbar.

   Note  The data in the new table you create does not inherit the field properties or the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) setting from the original table.

7. For each table in the new database, create the same index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) used in the replica's table.

   How?

   1. Open a table in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
   2. In the upper portion of the window, click the field that you want to create an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) for.
   3. In the lower portion of the window, click in the Indexed property box, and then click Yes (Duplicates OK) or Yes (No Duplicates).
8. For each table in the new database, create the relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) that existed for the replica's table.

   How?

   When you create a relationship (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between tables, the related fields don't have to have the same names. However, related fields must have the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) unless the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) field is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field. You can match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.

   Define a one-to-many or a one-to-one relationship

   1. Close any tables you have open. You can't create or modify relationships between open tables.
   2. Press F11 to switch to the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.).
   3. Click Relationships on the toolbar.
   4. If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed.

      If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.

   5. Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.

   6. Drag the field that you want to relate from one table to the related field in the other table.

      To drag multiple fields, press the CTRL key, click each field, and then drag them.

      In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table.

      

   7. The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.

      Set the relationship options if necessary.

   8. Click the Create button to create the relationship.
   9. Repeat steps 5 through 8 for each pair of tables you want to relate.

      When you close the Relationships window (Relationships window: A window in which you view, create, and modify relationships between tables and queries.), Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

   Note  You can create relationships using queries as well as tables. However, referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) isn't enforced with queries.

   Define a many-to-many relationship

   1. Create the two tables that will have a many-to-many relationship (many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.).
   2. Create a third table, called a junction table, and add to the junction table new fields with the same definitions as the primary key fields from each of the other two tables. In the junction table, the primary key fields function as foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.). You can add other fields to the junction table, just as you can to any other table.
   3. In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an Order Details junction table, the primary key would be made up of the OrderID and ProductID fields.

      How?

      1. Open a table in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
      2. Select the field or fields you want to define as the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.).

         To select one field, click the row selector (row selector: A small box or bar that, when clicked, selects an entire row in table or macro Design view, or when you sort and group records in report Design view.) for the desired field.

         To select multiple fields, hold down the CTRL key and then click the row selector for each field.

      3. Click Primary Key on the toolbar.

      Note  If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes window (Indexes window: In an Access database, a window in which you can view or edit a table's indexes or create multiple-field indexes.), and then reorder the field names for the index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) named PrimaryKey.

   4. Define a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) between each of the two primary tables (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.) and the junction table.
9. Save your new database.