Print Page | Close Window

Data Dictionary, Display Control

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=610
Printed Date: 28 Mar 2024 at 3:14am
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: Data Dictionary, Display Control
Posted By: Crystal Long
Subject: Data Dictionary, Display Control
Date 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 - 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 -
    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 -
    http://allenbrowne.com/noyesno.html  

    Outer join queries fail on Yes/No fields
    http://allenbrowne.com/bug-14.html - 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 - http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

free Access video tutorials:
http://www.YouTube.com/LearnAccessByCrystal - 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 ~



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net