Synchronize Model and Database

As you set up your database model structure, you can exchange objects between the model and a test database, using the Database Builder to routinely and easily compare the model and database at different levels and merge specific changes - or differences -  from one to the other.

Initially, you could also use the facility to populate a skeleton model structure with database objects imported from an existing database. Eventually, having developed your model, you could equally generate DDL for the whole model and populate a new database such as the final production database.

In all cases, you would establish a valid ODBC connection with the database in order to compare data objects, import from the database or generate DDL to add to the database. The system will, by default:

·Use the current active connection specified in the Database Builder, or
·Automatically activate the single connection specified in the Database Builder, or
·If there are several connections specified but none active, display a message prompting you to select from the list of possible connections, or
·If there are no connections defined, prompt you to create one

 

Access     Tools | Database Builder | Right-click on object | Show Differences

The Database Compare tab displays, showing the results of the comparison.

Field/Option/Button

Action

Upper Panel

EA

Lists the database objects that are present in the model.

Blank lines in the list indicate where an object exists in the database that is not in the model.

 

Action

Defaults to No Action. Click on the drop-down arrow to specify what action to take on all items in the object when you complete synchronization.

Paired objects

·No Action - do not update the database or model with this change
·===> - update the object in the database from the model
·<=== - update the object in the model from the database
·Customize - set the items to No Action prior to setting different actions on each item in the lower panel
·Unpair - separate the paired objects so that they are not compared with each other or updated from each other

 

Unpaired objects

·Create <object name> - create the missing database object in the database or model, as appropriate
·Delete <object name> - delete the object from the model
·Drop <object name> - delete the object from the database
·Pair with <object name> - pair the object in the database with the named (unpaired) object in the model, so that they are compared for differences between them

 

The Action fields in the lower, Item, panel default to this value or the appropriate action to support this value.

For example, if the database has a Table column Address1 and the model does not have the column, setting the object Action to ===> (update the object in the database from the model) sets the column Item Action to Drop Address1 to remove the column from the Table in the database.

 

ODBC

Lists the database objects that are present in the linked database, paired to the objects in the model where the model also contains those objects.

Blank lines in the list indicate where an object exists in the model that is not in the database.

 

Count

Indicates the number of items (components of any type) in the object that show differences between the model and database.

 

Case Sensitive

Select this checkbox if the comparison should recognize differences in letter case.

 

Use Alias if Available

Select this checkbox if the comparison should compare objects by Alias as well as by name.

 

Reset All

Set all the listed objects back to the Action value No Action.

 

Set Import All

Set all the listed items to the Action value <=== (update the items in the model from the database).

 

Set Synchronize All

Set all the listed items to the Action value ===> (add the items in the database from the model).

 

Lower Panel

Item

Lists the components of the selected item within each type of component (column or constraint). Expand the item to show the names and values of the items.

 

EA

Lists the values of the items within the selected database object, in the model.

 

Action

Defaults to the appropriate action corresponding to the setting of the object Action field, above. Click on the drop-down arrow and select the action to take on the item.

·No Action - do not update the database or model with this change
·===> - update the item in the database from the model
·<=== - update the item in the model from the database
·Unpair - separate the paired items so that they are not compared with each other or updated from each other
·Pair with <item name> - pair the item in the database with the named (unpaired) item in the model, so that they are compared for differences between them
·Add <item name> - create the missing item in the database or model, as appropriate
·Delete <item name> - delete the item from the model
·Drop <item name> - delete the item from the database

 

(You can actually change each item Action regardless of whether you have selected the Customize option in the  object Action field.)

 

ODBC

Lists the values of the items within the selected database object, in the database.

 

Count

Indicates the number of items of each type in the selected object that show differences between the model and database.

 

Reset

Set all the listed items back to the Action value No Action.

 

Import from ODBC

For all objects and items set to be updated in the model from the database, perform the update.

 

Generate DLL

For all objects and items set to be updated in the database from the model, generate the DDL.

The generated DDL is then displayed on the Execute DDL tab, to be executed to update the database.

 

Notes

·You cannot select to update or ignore specific parameter values, only all values of the item they belong to
·The best results are obtained by running the comparison at the top level; that is, the DBMS type; the differences are then listed at high-level and can be expanded in the lower panel rather than having a more detailed list in the upper panel

 

Learn more