Goal: Sunset redundant application and data architectures early in the process.

Strategy: Have our MS Access developers deploy database and application auditing mechanisms to gather statistics, which will be used to find unwanted objects to remove from redundant database applications.

Database Audit: Insert four metadata columns in every table within every MS Access database and modify the applications to support the new columns.


Application Audit


Help4Access MS Access developers implement every form and every report, add a new function that is activated on, on open, after update (form only), and on close. The function will insert the object name, the system’s date and time, the current user, and the event action into the new usys_application_audit table.

Example data produced within usys_application_audit table:

frmPassword, 201509140700, John Doe, Opened.
frmCustomer, 201509140819, John Doe, Opened.
frmCustomer, 201509140822, John Doe, Updated.
frmCustomer, 201509140821, John Doe, Closed.
frmPassword, 201509140730, John Doe, Closed.

Deploy the modified MS Access applications and gather application and database usage statistics.  Find candidate objects for sunsetting and retire those tables, forms and reports that are not supporting a business function. Retiring may involve just renaming the objects to OLD_(object name). The goal of this stage is to separate the wheat from the chaff, so to speak, before we mill/process the most valuable part: the customer’s data.

Application Transformation Management (Iterative)

s2bIn order to have the capability deploy transitional application architectures, it’s necessary to add the feature into the application layer to allow the administrator to control which objects (forms and reports) a user within a group may execute. This capability is necessary to incrementally sunset portions of the application whenever you sunset a feature or move a feature one application to another during application consolidation.

The programming logic will require the development of a user-role-application object-rights matrix, and the supporting programming logic to be inserted into every database application.

Some of the objects that will be developed during this step are: a password form, usys_user_role_object_rights table, unified menu and navigation forms that are user-right aware, and usys_app_config_version_local and usys_app_config_server tables to allow administrators to sunset entire applications when a new release is available.