Data Dictionary, Display Control by Crystal

attached zip file explodes into an Access 2007 database:
DataDICTIONARY_DisplayControl_Crystal.accdb

uploads/84/DataDICTIONARY_DisplayControl_Crystal.zip

Zip file contains 2 objects: 1 form and 1 module:

• f_DataDICTIONARY_DisplayControl
• mod_crystal_DataDICTIONARY_DisplayControl

How to Use this tool:

import the DataDICTIONARY_DisplayControl form and module into a working database, then compile and save, then Open the form: f_DataDICTIONARY_DisplayControl

Overview

• View Data Dictionary for selected table
  
  
• go to Table Design view of selected table
  
  
• open table Datasheet View of selected table
  
  
• Rename selected table
  
  
• See if there are text or memo fields where Unicode Compression is not set
  
  
• see an estimate of record width  (sum of the data type sizes, taking compression into account)
  
  
• Change Display Control of selected fields:

1. Combo and Listbox to Textbox
   When changing to Textbox, SQL and other settings will not disappear until Compact/Repair.
   
   It is not a good idea to use lookup fields once you begin developing the user interface. In web databases, however, this does not apply since they are different and Lookup fields are used to define relationships. 
   
   The  Evils of Lookup Fields in Tables
   http://www.mvps.org/access/lookupfields.htm
   
   
2. Integer to Checkbox
   
   To avoid using the Yes/No data type, which is stored as byte, can cause issues, and cannot contain Null, set the data type to Number and the Size to Integer.  Then change the DisplayControl to a checkbox in the Table Design.
   
   Why I stopped using Yes/No fields
   http://allenbrowne.com/noyesno.html 
   
   Outer join queries fail on Yes/No fields
   http://allenbrowne.com/bug-14.html

Choose Table

When you first open the f_DataDICTIONARY_DisplayControl form, you will not see much until you choose a table to look at.

CHOOSE A TABLE

Choose a table from the combo box. Use the filter Table textbox like the SearchBar in the Navigation Pane -- fill it out then press TAB.  You will see the Data Dictionary for the selected table as well as information about Display control and Unicode Compression.

Delete Lookups

The field list is a Simple Select list allowing multiple values.  Click on a field to toggle the selection.

The example table has:

3 Lookup fields
,and
needs Unicode compression to be set on 2 fields: one text field and one memo field

NOTE: Size is 0 (zero) for memo fields since they are stored in a separate structure and space grows to accommodate the data.  It is a good idea to keep memo fields in a different table (which may be linked) since memo fields can cause issues.

Rename Tables

This tool can be used to rename tables. Select a table, fill the New Name textbox and click the Rename command button.

If Check Name is true, then the specified new name is compared with known bad characters (edit the Get_CorrectName code to include or exclude whatever you want).  The first character cannot be a number.  A better new name will be suggested.  If acceptable, click Yes and the table will be renamed.

When the action is completed, the Table combo will show the new table name.

Delete Lookups

The field list show that there are 3 lookup fields with DisplayControl set to ComboBox or ListBox.

As selections are made, the SQL for the selected lookup controls is displayed on the right:

Click Combo/Listbox to Textbox to make the change and see  a report of what was done:

Integer to Checkbox

Select Integer fields to set DisplayControl to Checkbox

Click Integer to Checkbox to make the change and see the report:

Fieldlist shows that DisplayControl is a Checkbox

Table Design also shows that DisplayControl is now Checkbox (106)

Unicode Compression

If there are any text or memo fields that need unicode compression to be set (such as databases converted from older versions), indicators will display.  You can make these changes yourself.

Table Design

click on the DESIGN VIEW button to look at table design and make changes

Datasheet

click on the DATASHEET view button to see the data

Warm Regards,
Crystal

Microsoft MVP
remote programming and training

Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

free Access video tutorials:
http://www.YouTube.com/LearnAccessByCrystal

 *
   (: have an awesome day :)
 *