In Microsoft Office Access 2007, replicated databases that were created in earlier versions of Access (.MDB format) cannot be saved in the new .ACCDB file format. The option appears dimmed (unavailable) when you try to use the Save Database As command (click the Microsoft Office Button , and then click the arrow next to Save As).
The .ACCDB file format does not support replication.
Use the replicated database in the .MDB file format
You can continue to use your replicated database in the .MDB file format. Replication functionality is still supported for the .MDB file format.
Manually re-create the database in Office Access 2007 file format
If you decide that the new file format offers benefits that outweigh the benefits you get from replication, you can create a new database in Office Access 2007 format, and manually re-create the replicated database.
Note This method will work on any replicated database, but unless you are working with a full replica that has been synchronized with all other replicas in the replica set, any data and projects that are not already in your replica will not be in the new database.
Before you begin re-creating your database, you should make sure that all of the hidden and system objects in the replica are visible. This is to ensure that you can access the replica-specific fields in the replica when you re-create it.
- Open the replica that you want to convert. You must open the replica by using the same version of Access that was originally used to create the replica, to display the hidden and system fields.
- On the Tools menu, click Options. The Options dialog box opens.
- On the View tab, in the Show section, select Hidden objects and System objects. Click OK to apply the new settings and close the Options dialog box.
Manually re-create the database
- Create a blank database and open it.
Note Access creates a new, empty table named Table1. You should delete this table — you can do this by immediately closing it before you make any changes to it. Right-click its object tab and then click Close on the shortcut menu.
- On the External Data tab, in the Import group, click Access.
The Get External Data - Access Database dialog box appears.
- Click Browse to locate the replicated database.
- Click the replicated database that contains the database objects that you want to recreate in the new database, and then click Open.
- In the Get External Data - Access Database dialog box, click Import tables, queries, forms, reports, macros, and modules into the current database, and then click OK.
- Click the database objects that you want to import into the new database. If you want to import all the objects, click Select All on each tab.
Important Do not select any tables at this point. You will re-create the tables in a later step.
- After you have finished selecting objects, click OK to import them.
On the last page of the wizard, you have the option to save the import steps for future use. To do so, select the Save import steps check box, enter the appropriate information, and then click Save Import.
- Open the replicated database in Office Access 2007. For each table in the replicated database, 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.
Note 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 (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 should include 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.
By default, the s_GUID, s_Lineage, and s_Generation fields are hidden. To display these fields, use the following procedure:
- In the replicated database, make sure all tables are closed.
- Right-click the top of the Navigation Pane, and then click Navigation Options. The Navigation Options dialog box opens.
- In the Display Options section, select Show System Objects, and then click OK.
- Create a query, selecting the tables that contain the records you want to put in the new table.
- In query Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), on the Design tab, in the Query Type group, click Make Table. The Make Table dialog box appears.
- In the Table Name box, enter the name of the table you want to create or replace.
- 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.
- Click OK.
- On the Design tab, in the Results group, click Run. This will make the new table.
Note 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.
- 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.) and primary key used in the replica's table.
- 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.
When you create a relationship 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 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
- Close any tables you have open. You can't create or modify relationships between open tables.
- On the Database Tools tab, in the Show/Hide group, click Relationships.
- 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, invoke it: On the Design tab, in the Show/Hide group, click the Show Table button.
- 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.
- 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.
- 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.
- Click the Create button to create the relationship.
- Repeat steps 4 through 7 for each pair of tables you want to relate.
When you close the Relationships window (Relationships window: An object tab 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.
Define a many-to-many relationship
- 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.).
- 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. You can add other fields to the junction table, just as you can to any other table.
- 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.
- Open the table in Design view.
- Select the field or fields you want to define as the primary key.
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.
- On the Design tab, in the Tools group, click Primary Key.
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 the Indexes button in the Show/Hide group 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.
- 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.
- Save your new database.