Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - Data Dictionary, Display Control
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

Data Dictionary, Display Control

 Post Reply Post Reply
Author
Message
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: Data Dictionary, Display Control
    Posted: 27 Apr 2013 at 6:48pm

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.

Menu: Data Dictionary, Display Control

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 TABYou 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

3 Lookup fields, Need Unicode Compression

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.

Rename Table with bad characters

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.

List of Tables with New Name chosen

Delete Lookups

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

3 Lookup fields to change

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

SQL displayed for selected fields

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

Click Combo/Listbox to Textbox to see  a report of what was done

Integer to Checkbox

Select Integer fields to set DisplayControl to Checkbox

Select Integer fields to set DisplayControl to Checkbox

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

Integer to Checkbox Report

Fieldlist shows that DisplayControl is a Checkbox

Table Design shows that DisplayControl is changed to Checkbox (106)

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

Table Design shows that DisplayControl is also 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

Table Design Icon

Datasheet

click on the DATASHEET view button to see the data

Datasheet View

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 :)
 *



Warm Regards,
Crystal
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together

http://www.AccessMVP.com/strive4peace
http://YouTube.com/LearnAccessByCrystal
~have an awesome day ~
Back to Top
Sponsored Links


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down