Interactions between database diagrams, table design windows, and the database (ADP)

When you connect to a database and begin designing or modifying a database diagram or a table, Microsoft Access retains your work in memory. That is, Access does not transmit your work to the database until you explicitly save the work there. Regardless of how many database diagrams you open or how many tables you design, Access retains a single in-memory model of the database structure. There are several ramifications:

• You can experiment with different object definitions

  Because your modifications are not saved to the database immediately, you can experiment to see how a proposed modification will affect the database. When you complete you modifications, you can either save your changes to the database or discard your changes.

• Your modifications can appear in many database diagrams or table design windows

  When you modify a database object, every open database diagram containing that object will reflect that modification. For example, if you add a column to a table, the new column appears on every open database diagram containing that table. If you modify an object and later add that object to another database diagram, the added object reflects the modifications— even if you have not yet saved the modifications to the database.

• Your modifications can exist in memory only

  The in-memory model of the database structure endures until you close all database diagrams and table design windows for that database. Thus, it is possible that the in-memory model retains modifications that are not visible on any open database diagrams. Even if you remove a modified object from the only open database diagram, the modification remains in memory. If you later add the same object to any database diagram, the modification will be visible.

When you save your work from a database diagram or Table Designer, the Save operation transmits your additions and modifications to the database as a series of SQL statements. Several issues can arise during this process:

• Your modifications can require the deletion and re-creation of some database objects.

  Many databases support a limited set of ways you can modify an existing object. A typical limitation disallows the reordering of columns. But you can use the Table Designer to reorder a table’s columns, even if you are connected to a database that does not directly accommodate the reordering of columns, such as Microsoft SQL Server. To circumvent the database limitation, the Save operation actually creates a new table with the desired characteristics and deletes the existing table.

  The limitation disallowing column reordering is merely an example; there are several such limitations. The Save operation uses the delete-and-re-create technique to circumvent many of these limitations. When using the delete-and-re-create strategy, the Save operation preserves any rows of the table, as well as foreign-key relationships and other settings.

• Your modifications might indirectly affect more database objects than you realize.

  In some cases, the Save operation will delete and re-create several tables because those tables are related to a table you have modified.

  For example, if you change the data type of the au_id column in the titleauthor table, the Save operation must preserve the existing relationships from titleauthor to titles and authors. To preserve these relationships, the Save operation must re-create all three tables and both relationships.

  Because the preservation of relationships can induce delete-and-re-create operations on several related tables, the save operation can yield the Save Dialog Box, which warns you about which tables will be affected by the save operation. The Save dialog box appears before your modifications are transmitted to the database, so you can see which table will be affected before choosing to proceed with or cancel the save operation.

• Another user might have recently modified some database objects that you are now trying to modify.

  As you use a database diagram or the Table Designer to modify database objects, another user might be modifying the same objects. If that other user saves his or her work before you do, your subsequent Save operation will detect that recent changes to the database have affected the objects you are trying to modify. To help you decide how to proceed, the Database Changes Detected Dialog Box appears. The Database Changes Detected dialog box appears before your changes are transmitted to the database, so you can see which recent modifications you will be overwriting if you choose to proceed with the save operation.

• Before transmitting your modifications, Microsoft Access tries to anticipate problems with the Save operation.

  There are several reasons why your save operation might fail. For example, if the save operation induces an implicit delete-and-re-create operation on a table that you do not have permission to delete, your operation can fail. Similarly, if one of your modifications would yield an index that exceeds the maximum size for indexes, your operation can fail. Although the database itself is the final arbiter of which modifications are legal and which are not, Microsoft Access will try to anticipate how your modifications might fail. To warn you of these potential problems, the Validation Warnings Dialog Box appears. It appears before your modifications are transmitted, so you can choose whether or not to proceed.

• The implicit delete-and-re-create operation does not repopulate full-text indexes.

  If your save operation induces an implicit delete-and-re-create operation on a table that has full-text indexing, the index will be re-created, but the full-text catalog will not be repopulated. You should repopulate the full-text catalog after all of your modifications are complete. To remind you of any full-text indexes that need to be repopulated, the Post-Save Notifications Dialog Box appears. It appears after the database actually attempts to satisfy your transmitted save operation.

• The database might complete only a portion of the transmitted Save operation.

  Remember, thedatabase is the final arbiter of which modifications are valid and which modifications you have permission to complete. When you transmit your save operation to the database, some objects might be modified and some might not, based on permissions, constraints, or any of several other factors. To inform you of any failures in the save operation, the Post-Save Notifications Dialog Box appears.