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:
- 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
- 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.
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 LookupsThe 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 - 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 ~
|